Hello, I have the following query that uses another query. As I understand the runtime behavior, this second query is called again every time the main query creates a new record. Both queries are actually quite fast. Unfortunately, the overall construct becomes very slow when I call them together. Can someone tell me how I can avoid unnecessary re-iteration of the subquery? Example With X1 As ( SELECT COUNT(Employee_ID) As NumberOfEmployees, TBL_Employees ) SELECT MyTable1.MyField1, MyTable3.MyField2, X1.NumberOfEmployees, X1.EntryDate FROM MyTable1 INNER JOIN MyTable3 ON MyTable1.MyField5 = MyTable3.MyField4 CROSS JOIN X1 Query X1 needs less then 1 second also the main Query does if I eleminate query X1 of it. It seems X1 runs wich each record the main query delivers. Any idea how I can speed up this query?

First of all, the query you posted is not correct. The CTE selects two columns, but there is no FROM clause. It seems that in your ambition to mask the table and column names, you managed to distort the query. I can make the guess that the command in the CTE is supposed to be FROM, and in that case, the simple answer would be: Save the count in a variable and skip the CTE. If that was not the correct answer, post your actual query without modification. Alternatively, copy the database, rename table and colunm names and run the altered query to make sure that it has the behaviour you describe.

Subquery makes complet code very slow

Accepted answer

Erland Sommarskog 105.8K Reputation points MVP

2024-02-24T16:49:57.22+00:00

First of all, the query you posted is not correct. The CTE selects two columns, but there is no FROM clause. It seems that in your ambition to mask the table and column names, you managed to distort the query.

I can make the guess that the command in the CTE is supposed to be FROM, and in that case, the simple answer would be: Save the count in a variable and skip the CTE.

If that was not the correct answer, post your actual query without modification. Alternatively, copy the database, rename table and colunm names and run the altered query to make sure that it has the behaviour you describe.
Please sign in to rate this answer.

1 person found this answer helpful.
Hans Furmann 40 Reputation points

2024-02-25T09:20:25.0566667+00:00

First of all, you're right!
The statement should look like this:

With X1 As ( SELECT COUNT(Employee_ID) As NumberOfEmployees FROM TBL_Employees )

I tried it an it works! Thank you.
Unfortunately, an additional problem now arises. I have to calculate the number of employees on a monthly basis for one year at a time. Then I get 12 rows with 2 fields each (number of employees and year/month. How do I solve this with a query that runs only once? Sure, I can summarize the result in a variable using STUFF, but how can I create the required result set with 12 rows and 2 fields from this variable? Or is there for this problem a better solution that doesn't significantly deteriorate performance?

Erland Sommarskog 105.8K Reputation points MVP

2024-02-25T11:29:33.99+00:00

The normal thing would be to use a CTE. But since it seemed in the query you posted, that you were only using a scalar value, the variable seems like the easy workaround.

Since I don't anything about your tables or what you are trying to achieve, it's difficult to come with suggestion. But are really sure the the CROSS JOIN is the thing you need? Before you start thinking about performance, work with a small data set to verify that you have the logic correct,

Hans Furmann 40 Reputation points

2024-02-25T13:56:48.42+00:00

The Select ... From Employees delivers only 12 data rows The other select close to 1000 I know CROSS JOIN is not a performance miracle, that's why my question is how I can realize that the query that returns 12 records is only iterated once and not for each record from the second statement.

Erland Sommarskog 105.8K Reputation points MVP

2024-02-25T14:11:43.1966667+00:00

So the idea with SQL is that it is a declarative language. That is, you tell what result you, and the optimizer figures out the best way to compute it.

No, that does not always work out as intended. As Dan said in his comment, please share the actual execution plan if you want assistence with it.

And, again, verify that the query returns the correct result.
Sign in to comment

Share via

Subquery makes complet code very slow

0 additional answers