Trace flag 8690 eliminates the sort as well as the table spools. That query finishes in around 5.838 seconds on my machine. The original query in the question has an issue: SQL Server is doing a useless sort before the nested loop join. When '3' then (select value from D where D.clientId=100 and '3'=D.Id) When '2' then (select value from C where C.clientId=100 and '2'=C.Id) When '1' then (select value from B where B.clientId=100 and '1'=B.Id) You can get a nearly identical runtime and query plan by writing the query like this: SELECT LEFT JOIN D ON D.clientId=100 and D.Id = '3' LEFT JOIN C ON C.clientId=100 and C.Id = '2' LEFT JOIN B ON B.clientId=100 and B.Id = '1' This finished in around 2.314 seconds: SELECTĬOALESCE(B.column1, C.column1, D.column1, - omitted other columns (case when A.column1='1' then A.column1='2' then A.column1='3' then omitted other columnsįor another option you can write the query with joins (where we can rewrite the CASE expression in a more compact form, using COALESCE(). The query below finishes in around 1.044 seconds: DECLARE VARCHAR(100) = (select value from B where B.clientId=100 and B.Id = '1') ĭECLARE VARCHAR(100) = (select value from C where C.clientId=100 and C.Id = '2') ĭECLARE VARCHAR(100) = (select value from D where D.clientId=100 and D.Id = '3') You can save off the results into local variables and just use those in the query. The key is that the CASE expression is only ever going to return 3 (or 30) unique values if it finds a match. The most efficient way to write this query is without joins at all.
#MYSQL CASE IN SELECT SERIAL#
In any case, with serial queries we can't expect a better result than 0.7 seconds. I'm doing pretty unscientific tests because I don't know anything your data. I disabled result sets and ran the following query in SSMS: SELECT A.column1 SELECT TOP (5000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
INSERT INTO D VALUES (100, '3', 'TABLE D') INSERT INTO C VALUES (100, '2', 'TABLE C')
INSERT INTO B VALUES (100, '1', 'TABLE B') I think that 3 is enough to show the general principles. I wasn't game to create 30 tables so I just created 3 for the CASE expression. I mocked up some quick test data and put 10 million rows in table A. I'm assuming that you have appropriate indexes on the tables in the subqueries. Is there an efficient way to handle this in SQL Server? Each client has a specific ClientId and these tables B,C,D etc have index in place on Id and ClientId columns. In Tables B,C,D.so on, we maintain data for all clients. Then (select value from D where D.clientId=100 and A.column1=D.Id)Īnd so on uptil 30 more 'when' conditions Then (select value from C where C.clientId=100 and A.column1=C.Id) Then (select value from B where B.clientId=100 and A.column1=B.Id)
When then (select value from Table3)Īnd so on uptil 35 more 'when' conditions. (case when then (select value from Table1) I run a report in which I have a situation where based on a column value which is basically a Key or Id, I need to fetch corresponding value from the mapping Id table.
0 kommentar(er)
