Lowering CPU cost on an SQL Query

Question

I have a query like below where table150k has 150k records and table3m has 3m records. On our production servers, we have to run this query for a single record at a time very frequently. This costs a lot of cpu power.

select t.id, t1.field1 as f1, t2.field1 as f2, t3.field1 as f3, ..., t12.field1 as f12
from table150k t
inner join table3m t1 on t1.fk = t.id and t1.[type] = 1
inner join table3m t2 on t2.fk = t.id and t2.[type] = 2
inner join table3m t3 on t3.fk = t.id and t3.[type] = 3
...
inner join table3m t12 on t12.fk = t.id and t12.[type] = 12
where t.id = @id

When I remove inner joins from this query, it works fine. When they are included, our servers suffer cpu.

How should I optimize this query, data structure or scenario so that frequent fetches of data do not cost cpu as high?

When you remove the joins you are only selecting from a single table. Adding the joins increases the complexity so yes the CPU will increase. — CathalMF, Mar 25 '13 at 14:50
Patient: "Doctor, it hurts when I do this". Doctor: "Then stop doing that"... — jeroenh, Mar 25 '13 at 14:50
What indexes are on the tables involved and what does the execution plan look like? — Martin Smith, Mar 25 '13 at 14:52
why do you want to join the table3m multiple times? I mean how about inner joining table3m with in clause for type from 1 to 12 and then consolidating the records at the application level. — Ankit Bansal, Mar 25 '13 at 14:53
Are these 1-1 joins? How many duplicates are there for t.id? And, are you really selecting different fields from the tables, or is it: `t1.field as field1, t2.field as field2, . . .`? — Gordon Linoff, Mar 25 '13 at 14:53
@Martin Smith and Ankit I am afraid that that will pull 12 times more data, which will create another huge problem. — Serhat Ozgel, Mar 25 '13 at 14:55
@GordonLinoff These are 1-1 joins. There is exactly one record for a given user and type in table3m. I am always selecting the same field on table3m. — Serhat Ozgel, Mar 25 '13 at 14:56
@Mike_OBrien There is only one table3m. The data always come from that table. — Serhat Ozgel, Mar 25 '13 at 14:57
@SerhatÖzgel - No idea why you `@`-ed me in that comment. I asked you a question not gave you any suggestion. — Martin Smith, Mar 25 '13 at 14:59
Please try [SET SHOWPLAN_TEXT ON](http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx) and show us the results. — mbeckish, Mar 25 '13 at 15:01
@mbeckish I will do that but the actual query is a lot more complicated than this. I hope it will not be too much confusing. — Serhat Ozgel, Mar 25 '13 at 15:03
@AlexanderFedorenko A few, but lots of fields from some other joined tables are necessary. I did not include them in the sample to keep things simple. — Serhat Ozgel, Mar 25 '13 at 17:01

Gordon Linoff · Answer 1 · 2013-03-25T15:00:22.717

2

Do you have an index on table3m(fk)?

That should fix your problem.

An alternative formulation is:

select t.id,
       max(case when m.[type] = 1 then field end) as field1,
       max(case when m.[type] = 2 then field end) as field2,
       . . .
       max(case when m.[type] = 12 then field end) as field12
from table150k t join
     table3m m
     on m.fk = t.id and m.[type] in (1,2,3,4,5,6,7,8,9,10,11,12)
where t.id = @id
group by t.id

This structure has all the data coming from the same column in the "3m" table.

edited Mar 25 '13 at 15:00

answered Mar 25 '13 at 14:50

Gordon Linoff

1,242,037
58
646
786

How about on `table3m(fk + [type])`? – Joe Enos Mar 25 '13 at 14:52
@JoeEnos We have that too :( – Serhat Ozgel Mar 25 '13 at 14:58
I will give alternative formulation a try and post results. – Serhat Ozgel Mar 25 '13 at 15:04
Gordon's answer is right. SQL Server will process his query making a single pass over the table. You could also use the PIVOT operator. Regarding the index, table150k should be clustered on id. The table3m needs an index with fk and type, probably in that order but perhaps reversed. If only a small portion of table3m meet the criteria and it's not updated frequently, consider a materialized view of just those two columns. – James K. Lowden Mar 26 '13 at 07:19
@JamesK.Lowden - A single pass over the table isn't necessarily going to be better than multiple joins with correct indexes. [See plans and timings here](http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join/7449213#7449213) – Martin Smith Mar 26 '13 at 07:44
@Martin, your test results are interesting, and don't speak well of the efficiency of PIVOT which by rights should be faster than a bunch of CASE ... MAX clauses (since the server has more information about the intent). As usual, "it depends". – James K. Lowden Mar 30 '13 at 22:09

score 0 · Answer 2 · edited Mar 25 '13 at 14:54

0

If the data in the two tables does not change frequently I follow an alternate method to create a caching table(just another table) which just holds the result of the above query.

edited Mar 25 '13 at 14:54

DOK

32,337
7
60
92

answered Mar 25 '13 at 14:51

Arvind Singh

733
1
10
31

2

business logic needs that data to be instant :( – Serhat Ozgel Mar 25 '13 at 14:51

score 0 · Answer 3 · answered Mar 25 '13 at 14:51

0

Try this:

select *
from table150k t
inner join table3m t1 on t1.fk = t.id and t1.[type] in (1,2,3,4,5,6,7,8,9,10,11,12)
where t.id = @id

answered Mar 25 '13 at 14:51

CathalMF

9,705
6
70
106

Will not this produce 12 times more records? – Serhat Ozgel Mar 25 '13 at 14:53
its an inner join, not left join – Green Demon Mar 25 '13 at 14:58
It still will fetch 12 times more records because each row will be duplicated for each record in table3m. – Serhat Ozgel Mar 25 '13 at 14:59
On this example it looks simple but on actual production code, there are a lot of columns other than t1.Id and table3m.field1. All those will be replicated 12 times. – Serhat Ozgel Mar 25 '13 at 15:01
@SerhatÖzgel - That's true, but it still might perform better. If you try it out and this does perform better, it might be worth changing your app to expect one record per detail value, rather than one column per detail. – mbeckish Mar 25 '13 at 15:03
@mbeckish Fair enough. Will give it a try. – Serhat Ozgel Mar 25 '13 at 15:05

score 0 · Answer 4 · answered Mar 25 '13 at 15:06

0

select t.id, t1.type, t1.field1
from table150k as t
inner join table3m as t1 on t1.fk = t.id 
where t.id = @id and t1.[type] in (1,2,3,4,5,6,7,8,9,10,11,12)

This will bring back 12 records(assuming they all exist).

The advantage here is speed on the server side, disadvantage is you will have to map each record to a corresponding column in a datatable or value on an object based on the type value once you get it into your application.

answered Mar 25 '13 at 15:06

Mike_OBrien

1,395
15
34

the problem is that this code melts all the different [type] into a single column, whereas the OP wants them separated in different columns – Green Demon Mar 25 '13 at 15:12
Yes this will get one value record per type. thats why I qualified it by saying he'll have to reorganize the data once he gets it into his application. Its a bit more work in the data access layer but its a hell of a lot quicker than 12 inner joins on the same table. – Mike_OBrien Mar 25 '13 at 15:18

Lowering CPU cost on an SQL Query

4 Answers4