Joining two left join queries

i have two queries the first one :

 Select VCRNUM_0 As 'Tranx_Number', ACTQTY_0 As 'Quanity', sto.CREDAT_0 As 'Create_Date', sto.PROD_DATE_0, sto.PROD_TIME_0, sto.CREUSER_0, sto.ITMREF_0, sto.VCRNUMORI_0 as 'Work_Order_number', itm.ITMWEI_0, sto.ACTQTY_0 * itm.ITMWEI_0 As Weight, itm.ITMDES1_0 as 'SKU_Description', a1.TEXTE_0 As 'Work_Center_Description', gope.CPLWST_0 as 'Work_Center_Number'  From ZSTOJOU sto JOIN MFGOPE gope on sto.VCRNUMORI_0 = gope.MFGNUM_0 LEFT JOIN ATEXTRA a1 ON gope.CPLWST_0= a1.IDENT1_0 and a1.CODFIC_0 = 'WORKSTATIO' and a1.ZONE_0='WSTDESAXX' AND a1.LANGUE_0='ENG' LEFT JOIN APLSTD APL ON sto.TRSTYP_0= APL.LANNUM_0 and APL.LAN_0= 'ENG' and APL.LANCHP_0 = 704 LEFT JOIN ITMMASTER itm on sto.ITMREF_0=itm.ITMREF_0 Left Join ATEXTRA a2 On itm.TSICOD_6=a2.IDENT2_0 and a1.CODFIC_0 = 'ATABDIV' and a2.ZONE_0='LNGDES'AND a2.LANGUE_0='ENG'And a2.IDENT1_0=26 WHERE sto.TRSTYP_0 =5 and  sto.VCRTYPORI_0=10 and sto.VCRTYPREG_0 = 0  AND gope.CPLWST_0 NOT IN('22500L','22600L','225C0L','612B0l','611A0L','214G0','81000L','22050')  and gope.CPLWST_0 is not null 

then i have second query :

Select TOP 5 ITMMASTER.ITMREF_0, ITMMASTER.ITMDES1_0,ITMMASTER.TCLCOD_0, a3.TEXTE_0 from ITMMASTER join ITMCATEG cat ON ITMMASTER.TCLCOD_0= cat.TCLCOD_0 LEFT Join ATEXTRA a3 ON ITMMASTER.TCLCOD_0= a3.IDENT1_0 and a3.CODFIC_0 = 'ITMCATEG' and a3.ZONE_0='TCLAXX' AND a3.LANGUE_0='ENG' Where ITMMASTER.ITMREF_0 ='2AL00HR0' 

my question is how do i left join the two.?

MariaDB views: I want to replace repeated multi-table joins in my queries with a view – are there any issues to watch out for?

Rather than

SELECT a.pk, b.pk, c.pk, d.name  FROM a JOIN b on b.pk = a.fk JOIN c on c.pk = b.fk JOIN d on d.pk = c.fk 

I can do

SELECT a_pk, b_pk, c_pk, d_name  FROM view_a_b_c_d 

I have a lot of this sort of thing through my code.

I’ve done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.

But before I commit to that (as it’d be a big change with a lot of work and testing), I want to check that this IS a good thing to do. I didn’t study computer science and have no formal DBA training. I’m also a sole dev working on my own closed-source product. So I don’t get much input from the outside world, unless I strike out and ask for it.

Thank you – any opinions/experience appreciated.

Postgres Combine Summed Values from 2 Queries / Tables into Single Row

Say I had the following 2 queries, summing values from separate tables.

I would like the sum of recorded time

SELECT      SUM(minutes) as recorded_minutes,     SUM(hours) as recorded_hours FROM recorded_time WHERE     project_id = 1 

To be combined with the sum of budgeted time in a single row

SELECT      SUM(minutes) as budgeted_minutes,     SUM(hours) as budgeted_hours FROM budgeted_time WHERE     project_id = 1 

Is it possible to do this in a single query?

How does Google rank different queries?

I’m looking for help from a backend expert or someone who can teach me more about the process search engines use to code, value, or weigh different search queries around the same topic. I also have a few specific questions about how interlinking between different sites work. I want to know if there are resources to learn more about how search engines are generally coded and developed.

I have reviewed Google’s official guidelines but am looking for something beyond Googlebots, crawlers, etc. I’m aware of the different factors that affect a site’s rankings, but I’m looking for something more technical about the algorithm itself.

What can cause higher CPU time and duration for a given set of queries in trace(s) ran on two separate environments?

I’m troubleshooting a performance issue in a SQL Server DR environment for a customer. They are running queries that consistently take longer in their environment than our QA environment. After analyzing traces that were performed in both environments with the same parameters/filters and with the same version of SQL Server (2016 SP2) and the exact same database, we observed that both environment were picking the same execution plan(s) for the queries in question, and the number of reads/writes were close in both environments, however the total duration of the process in question and the CPU time logged in the trace were significantly higher in the customer environment. Duration of all processes in our QA environment was around 18 seconds, the customer was over 80 seconds, our CPU time was close to 10 seconds, theirs was also over 80 seconds. Also worth mentioning, both environments are currently configured to MAXDOP 1.

The customer has less memory (~100GB vs 120GB), and slower disks (10k HHD vs SSD) than our QA environment, but but more CPUs. Both environments are dedicated to this activity and should have little/no external load that wouldn’t match. I don’t have all the details on CPU architecture they are using, waiting for some of that information now. The customer has confirmed they have excluded SQL Server and the data/log files from their virus scanning. Obviously there could be a ton of issues in the hardware configuration.

I’m currently waiting to see a recent snapshot of their wait stats and system DMVs, the data we originally received, didn’t appear to have any major CPU, memory or Disk latency pressure. I recently asked them to check to see if the windows power setting was in performance or balanced mode, however I’m not certain that would have the impact we’re seeing or not if the CPUs were being throttled.

My question is, what factors can affect CPU time and ultimately total duration? Is CPU time, as shown in a sql trace, based primarily on the speed of the processors or are their other factors I should be taking in to consideration. The fact that both are generating the same query plans and all other things being as close as possible to equal, makes me think it’s related to the hardware SQL is installed on.

With respect to differential privacy how to find the global sensitivity of queries like ‘maximum height’ ‘Average height’ etc

As much as I have understood,for any query f(x), we need to take maximum of |f(x)-f(y)| over all neighboring databases.

please explain how to find global sensitivity of queries like average height or maximum height.

RDS – Mysql top CPU queries

I’d like to know if has any way to get the top queries in my instance per CPU where I would see which query has more impact in my instance based on CPU usage.

Anyone have any idea if it possible in Mysql like SQL Server?

How to answer the following queries on a tree?

Given a tree of "N" nodes(each node has been assigned a value A[i],node-"1" is the root of the tree), and a constant "K" , we have Q queries of the following type : [w]

(which means find the lowest valued node in the sub-tree of [w] , only considering those nodes in the sub-tree of [w] which have a depth less than equal to K) .

Example :

Value of nodes of tree :

A[1] = 10

A[2] = 20

A[3] = 30

A[4] = 40

A[5] = 50

A[6] = 60

Edges of tree : [1-2],

[2-3],

[3-4],

[4-5],

[4-6].

K=2.

Query-1 : [w]=1 . All nodes in subtree of [w] : (1,2,3,4,5,6) , now, all nodes in sub-tree of [w] having depth less than equal to K : (1,2) . Hence , minimum(A[1],A[2])=min(10,20)=10 is the answer .

Query-2 : [w]=4 . All nodes in subtree of [w] : (4,5,6) , now, all nodes in sub-tree of [w] having depth less than equal to K : (4,5,6). Hence , minimum(A[4],A[5],A[6]) = min(40,50,60)=40 is the answer .

Reconstructing an Array via Time-Intensive Subset Queries

I am trying to design an algorithm for a problem, and the following is an auxiliary problem for which a good solution would imply a faster algorithm for the original problem.

I am given access to an array of numbers. However, I am only allowed to query it by specifying an arbitrary subset of indices, in response to which I am then given the sum of the elements at those positions. These queries are quite costly, specifically they run in time $$\tilde{O}(n^2)$$ time (where $$\tilde{O}(\cdot)$$ hides polylogarithmic factors). I want to determine the element at each index in the array (i.e. reconstruct the array) using as little time as possible.

Of course, it is possible to do this by querying each element on its own. This algorithm does $$n$$ queries and hence has total running time $$\tilde{O}(n^3)$$. I am wondering if there is a faster way. Adaptivity does not help with this problem, so any algorithm would have two steps: First, it executes a fixed sequence of queries, and then reconstructs all elements using the query answers. Ideally, both steps run in time $$o(n^3)$$. So far, any set of $$o(n)$$ queries that I looked at makes recovery impossible. This might be the case for any such set of queries (and my intuition screams that this is probably the case), but I cannot see a proof for this.

I’d love an answer that either shows a faster algorithm or proves that $$o(n)$$ queries are impossible, but answers with partial insights would also be great.

Why did “terminal commands” never get a version of SQL “parameterized queries”?

I was taught horrible bad practice when I initially "learned" SQL, which baked in user-submitted input with quotes and attempted to "escape" this (in the beginning, I didn’t even escape it at all…). I then had to spend many years unlearning this, to instead do things like:

SELECT * FROM table WHERE id = $1;  And then the $ 1‘s data is sent separately to the database, not part of the actual query string, to make it impossible for "SQL injections" to happen.

However, terminal commands frequently need to be sent untrusted user input, such as:

generate_PDF.exe --template="a path goes here" --title-of-report="arbitrary title from user" 

Every time I have to run such a command, I’m scared to death that my "terminal argument escape" function isn’t working correctly, or has some unknown bug, so that users can make a title along the lines of "; rm -rf /; to execute arbitrary code on my machine.

This becomes even more of a serious issue when the normal "OS quotes" cannot be used, such as:

pg_dump --format custom --file "a real path" --exclude-table="schema name"."table name" 

The "schema name"."table name" part has to be provided in full from the user, and thus I have to attempt to verify the syntax myself, as it cannot just be quoted in its entirety with the "terminal argument escaper" function wrapping it all. (Even if it might be possible in this specific context, I’m talking in general and just using this as an example of when it gets "hairy".)

This has made me wonder why the terminal commands, for example in PHP (since I use this myself for everything) cannot be done like this:

pg_dump --format custom --file $1 --exclude-table=$  2 

And then we send the actual arguments separately as an array of strings, just like with the "parameterized queries" in SQL databases?

Please note that the $1 and $ 2 here do not refer to PHP variables, but to "placeholders" for the "engine" which interprets this and which lives either in PHP or the OS.

Why is this not a thing? Or maybe it is, only I haven’t heard of it? I’m continuously baffled by how many things which I constantly need and use just "sit there and rot" while they keep releasing a new programming language every week which nobody uses. I feel more and more frustrated about how "stale" everything I care about seems, but this risks getting off-topic, so I’ll stick to the question I’ve just asked for now.