May 14, 2012 at 8:47 am
Hi all,
I have a table which has 120m rows.
Another table which is used in the join with above table that has 2m rows.
I have written query on the first table with some group by.
;with src as (group by query)
select * from src
inner join table2 t2 on src.id = t2.id
Both of above tables are properly indexed.
The overall process takes around 1 hour to execute the query.
What is the issue.
And also, I need to retrieve another row from the above final query
I mean if the col1 in the above query contains value = 'OTHER', I need to retrieve the same row with col1 value = 'OTH'
SELECT Col1, Col2, Col3 FROM src
INNER JOIN table2 t2 ON src.Id = t2.Id
UNION ALL
SELECT 'OTH' AS Col1, Col2, Col3 FROM src
INNER JOIN table2 t2 ON src.Id = t2.Id
WHERE src.col1='OTHER'
What is the best way to write this query.
May 14, 2012 at 9:22 am
Start with reading the first link in my signature. It explains how to best post questions in order to receive an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2012 at 9:22 am
Tip 1 don't use select *
Edit, ok make that Tip 2
May 14, 2012 at 9:26 am
Without the DDL for the tables (including index definitions), sample data, expected results, your current code, and the actual execution plan(s) as .sqlplan files (ie, the graphical representation) there really isn't much we can tell you from what you have posted.
What you have given us is basically is what I have done doesn't work, here is an obfusication of what I have done (no real context), please tell me how to rewrite what I have done.
May 14, 2012 at 1:29 pm
Isn't the second half of your UNION just a subset of the first half?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 4:40 pm
a2zwd (5/14/2012)
Hi all,I have a table which has 120m rows.
Another table which is used in the join with above table that has 2m rows.
I have written query on the first table with some group by.
;with src as (group by query)
select * from src
inner join table2 t2 on src.id = t2.id
Both of above tables are properly indexed.
The overall process takes around 1 hour to execute the query.
What is the issue.
How many columns and rows are returned from that query?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2012 at 4:43 pm
Both of above tables are properly indexed.
The overall process takes around 1 hour to execute the query.
I gotta say, those two statements seem contradictory 🙂 or you're on a terribly underpowered box.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 14, 2012 at 4:46 pm
Well, we are still waiting for the DDL for the tables (including the index definitions), the code you are running, and the actual execution plans.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply