Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Need help optimizing a query Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 10:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
jrestuccio (4/9/2013)
Thank you SO MUCH for the replies, I am trying to take it all in.
Let me describe a little bit about why the data is deleted so regularly and why it's important to not slow down insert speeds.

Imagine 1000 searches being performed at one time, and as soon as the search is done, it collects the data and inserts the data into the TFX_v2_unfilteredrates table. The reason we need this to happen quickly is because we have a massive amount of searches to complete, and since the data needs to be FRESH it needs to be collected within 1 day.

The reason for deleteing the data is merely to keep the size of the table down to a workable size. Since I believe our queries are poorly written........

-Josh


Assuming that your hardware is spec'ed appropriately for the workload, even 1,000 inserts of 5,000 rows each (5 million rows total) probably shouldn't take more than five or ten minutes, even with a couple or three indexes to update. This code inserts 5 million rows into a test table - it ran on my DEV server in about 30 seconds:

create table dbo.insert_test (date_collected datetime, hotel_name varchar(20), hotel_id int, rate decimal(6,2))

go

set nocount on

declare @i int = 1

while @i<=1000

begin

insert into dbo.insert_test

select dateadd(day, (@i*-1), getdate()), 'xxxxxxxxxxxxxxxxxxxx', t.N, 1.32 * t.N

from .dbo.tally t

where t.N <= 5000

set @i = @i+1

end

If you're avoiding indexes to save a few minutes once a day when the table is loaded with new data, but the absence of indexes is costing you several minutes every time you run query, you're shooting yourself in the foot. Before you suffer through the poor performance of your query much longer, I'd investigate the impact of a proper indexing scheme on both the insert/delete process and the query performance to make sure you're striking an appropriate balance.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1440924
Posted Wednesday, April 10, 2013 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
Sorry, got inundated yesterday, but wanted to catch up with this. As mentioned, we really need everything for tuning assistance. The article describes the easiest ways to get it for us.

jrestuccio (4/9/2013)

Let me describe a little bit about why the data is deleted so regularly and why it's important to not slow down insert speeds.

Even with a clustered index you won't see a significant difference in insert speeds. Start doing indexed views and triggers and that's a different story, or overloading the non-clustered indexing.


Imagine 1000 searches being performed at one time, and as soon as the search is done, it collects the data and inserts the data into the TFX_v2_unfilteredrates table.

Not really an issue. I/O is I/O. You might even get better performance with a split index to avoid hotspotting and using a really loose fillfactor, but that's a different story.


The reason we need this to happen quickly is because we have a massive amount of searches to complete, and since the data needs to be FRESH it needs to be collected within 1 day.

The reason for deleteing the data is merely to keep the size of the table down to a workable size. Since I believe our queries are poorly written........

It's hard not to have poorly written queries when those queries have no indexes to work from. Get indexes into place and you may find you can actually keep your archive.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1440995
Posted Friday, April 12, 2013 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:42 AM
Points: 14, Visits: 51
Hey everyone, thanks again for the replies!
I am still a little confused on the best way to supply the DDL information, in the articles you link to it says the SP_help output should work, which I included in my spreadsheet.

Also, the "save execution plan" is greyed out in SSMS express, so what's the best way to get you the execution plan?

Again this is a SQL Server 2000 database.

Thanks again! Also, not sure if I am breaking any forum rules or anything (hope not) but how much would any of you charge as a consultant to take a hands on look?

-Josh
Post #1441743
Posted Friday, April 12, 2013 8:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
jrestuccio (4/12/2013)
Also, the "save execution plan" is greyed out in SSMS express, so what's the best way to get you the execution plan?


See the article in Craig's sig. Describes how to get an exec plan in SQL 2000, easiest way to save it is in a spreadsheet. Haven't looked at your spreadsheet to see what's in there, a little busy with stuff.

Thanks again! Also, not sure if I am breaking any forum rules or anything (hope not) but how much would any of you charge as a consultant to take a hands on look?


If by hands-on you mean remote, disconnected and across a couple of oceans, roughly $100/hr with a minimum of 8 hours. If you want in-person then I can't help you there.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1441747
Posted Friday, April 12, 2013 8:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Lynn Pettis (4/8/2013)
jrestuccio (4/8/2013)
Here you go, I tried to include all relevant information.
Again thanks for helping!

-Josh


2) Actual execution plan as a .sqlplan file, not XML plans you included in the spreadsheets.


SQL 2000 forum. Hence assumed to be a SQL 2000 server unless specified other. Hence no XML plans.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1441749
Posted Friday, April 12, 2013 9:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
GilaMonster (4/12/2013)
Lynn Pettis (4/8/2013)
jrestuccio (4/8/2013)
Here you go, I tried to include all relevant information.
Again thanks for helping!

-Josh


2) Actual execution plan as a .sqlplan file, not XML plans you included in the spreadsheets.


SQL 2000 forum. Hence assumed to be a SQL 2000 server unless specified other. Hence no XML plans.


Yea, missed that one.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441759
Posted Friday, April 12, 2013 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:42 AM
Points: 14, Visits: 51
SQL Server has encountered 146 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000438. The offset of the latest long IO is: 0x00000001224000

I also get these errors in the log when running the usp_newratesimport on bigger jobs (jobs containing more search results aka more rows to be processed)

I am still working on getting the execution plan.

Thanks

-Josh
Post #1441765
Posted Friday, April 12, 2013 9:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
jrestuccio (4/12/2013)
[C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2).


I suspect that's a good part of the problem. If I had to guess, lots of TempDB usage and TempDB still at default config on a not-so-fast drive.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1441775
Posted Monday, April 15, 2013 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:42 AM
Points: 14, Visits: 51
I've attached the execution plan as a .gif because I honestly couldn't figure out any other way to do it..........I looked at Kraig's guide and did as it said and pasted it into an excel spreadsheet.

Also, I tried creating an index on tfx_v2_unfiltered rates as follows
Timeinserted - asc
hotelid - asc
siteid - asc

It made the stored procedure actually run slower.
Query went from 16 seconds to 22 seconds.

Thanks

-Josh


  Post Attachments 
Query3_ExecutionPlan.GIF (7 views, 19.95 KB)
Post #1442496
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse