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 Monday, April 8, 2013 2:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 6:57 AM
Points: 9, Visits: 49
Just fair warning, I am definitely a newbie, I fell into this work so to speak.

I have a query that is taking between 30 seconds to 30 minutes depending on how much data is being run at the current time. I used SQL Server Management Studio Express to analyze the query and show me the execution plan with statistics ON.

I have since discovered where the problem is. I have one table in my database where we collect information, this information is updated daily. In fact it's not really updated, it's brand new data everyday collected from various websites.

Because this table collects brand new data daily it is not indexed, it's a HEAP. There are between 4-6 million rows of data on any given day depending on how much data is run that day. We clear all records older then 3 days from this table every night.

Now that I've explained the table a little, here's where the problem comes in: (This is not the entire stored procedure just the part that takes 98% operator cost time)

-------------------------------------------------------------------
-- GET THE RATES IN TEMP TABLE
-------------------------------------------------------------------
SELECT u.*,
NULL AS RateTypeID,
NULL AS RoomTypeID,
NULL AS RatePriority,
NULL AS RoomPriority
INTO #RESULTS
FROM TFX_V2_UnfilteredRates u
WHERE SiteID IN
(
SELECT * FROM #SITES
)
AND HotelID IN
(
SELECT * FROM #HOTELS
)
AND timeInserted > @JobDate

DROP TABLE #SITES
DROP TABLE #HOTELS


----------------------------------------------

It takes far too long to scan this table for the appropriate rates. Any suggestions/ideas on how to make this process run more efficiently would greatly be appreciated. I've thought about creating an index for the TFX_v2_unfiltedrates table which is the one that is 5million rows give or take. However I am worried that it would slow down my inserts on that table, since we need to insert data very quickly.

If you need more info please let me know, however I was trying to prevent from pasting the entire stored procedure.

Thanks

-Josh
Post #1440027
Posted Monday, April 8, 2013 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 PM
Points: 6,250, Visits: 7,409
Heya Josh,

You're most likely on the right track with the heap, but for optimization items there's a bunch of data most of us prefer to see before we start bounding through possibilities... primarily because we'd like to show you the right way on the first try.

If you take a look in my signature below, in the 'help on indexing/tuning' link there's a list of what we'll want and it walks you through getting it for us. Short form: All tables involved and their indexes (including the other tables), the query itself in full form, and the .sqlplan from an actual execution (as the heap stands now).

From there, we'll be able to analyze its connections and expectations and get you on track pretty quickly.



- 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 #1440042
Posted Monday, April 8, 2013 4:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 6:57 AM
Points: 9, Visits: 49
Here you go, I tried to include all relevant information.
Again thanks for helping!

-Josh


  Post Attachments 
Query_optimization_usp_newratesimport.zip (10 views, 38.45 KB)
Post #1440069
Posted Monday, April 8, 2013 6:59 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
jrestuccio (4/8/2013)
Here you go, I tried to include all relevant information.
Again thanks for helping!

-Josh


Nope, not what I'd want to see. I'd want to see the following:

1) DDL for the tables including the DDL for indexes
2) Actual execution plan as a .sqlplan file, not XML plans you included in the spreadsheets.
3) your current code



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 #1440092
Posted Tuesday, April 9, 2013 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 6:57 AM
Points: 9, Visits: 49
Did you look at all the sheets within the excel workbook?
It has the stored procedure (my code), also it is a SQL 2000 database and the save execution plan is greyed out when I try to save it.
Sorry for the silly question but what are you needing when you say DDL for all tables and indexes?
What's a quick way to pull that?

I thought I had this info on the "Relevant Tables and Indexes" sheet.

-Josh
Post #1440316
Posted Tuesday, April 9, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
jrestuccio (4/9/2013)
Did you look at all the sheets within the excel workbook?
It has the stored procedure (my code), also it is a SQL 2000 database and the save execution plan is greyed out when I try to save it.
Sorry for the silly question but what are you needing when you say DDL for all tables and indexes?
What's a quick way to pull that?

I thought I had this info on the "Relevant Tables and Indexes" sheet.

-Josh


What we want to see is ddl (create table statements and create index statements), sample data (insert statements). This is all detailed in the link in my signature about best practices when posting questions. The idea here is so that we can recreate your tables and such in our environment. We also need to see the actual execution plan.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1440345
Posted Tuesday, April 9, 2013 12:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 1,909, Visits: 19,091
try this index on your heap table......not quite sure why you are worried about data load speeds...seems you are already deleting data over three days old and adding new. Therefore I am going to assume you have a period at some time to perform this task. You could drop the index first and then recreate once loaded...that may help...but an index on your date may also assist the delete.

anyways...food for thought...here is some test data to paly with...its not a replica of your system ...but it does have 5M rows and some hotels/sites.

kind regards


USE [tempdb]
GO

DROP TABLE [dbo].[Hotel]
GO
DROP TABLE [dbo].[Site]
GO
DROP TABLE [dbo].[TransData]
GO

SELECT TOP 5000000 ---- NOTE 5 MILLION rows
TranID = IDENTITY(INT, 1, 1),
SiteID = 1 + CAST(Abs(Checksum(Newid()) % 10000) AS INT),
HotelID = 1 + CAST(Abs(Checksum(Newid()) % 1000 ) AS INT),
SalesAmount = CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2010', '2013'), '2010')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3


SELECT TOP 500
SiteID = 1 + CAST(Abs(Checksum(Newid()) % 10000) AS INT)
INTO Site
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
GO

SELECT TOP 50
HotelID = 1 + CAST(Abs(Checksum(Newid()) % 1000) AS INT)
INTO Hotel
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
GO

set statistics time, IO on

declare @jobdate as datetime
set @jobdate = '2011-07-09'

SELECT Hotel.HotelID, Site.SiteID, TransData.TransDate
FROM TransData INNER JOIN
Site ON TransData.SiteID = Site.SiteID INNER JOIN
Hotel ON TransData.HotelID = Hotel.HotelID
WHERE (TransData.TransDate = @jobdate)

set statistics time, IO OFF

/*create index*/

CREATE NONCLUSTERED INDEX [NIX_transdata] ON [dbo].[TransData]
(
[TransDate] ASC,
[HotelID] ASC,
[SiteID] ASC
) ON [PRIMARY]
GO

set statistics time, IO on

declare @jobdate as datetime
set @jobdate = '2011-07-09'

SELECT Hotel.HotelID, Site.SiteID, TransData.TransDate
FROM TransData INNER JOIN
Site ON TransData.SiteID = Site.SiteID INNER JOIN
Hotel ON TransData.HotelID = Hotel.HotelID
WHERE (TransData.TransDate = @jobdate)

set statistics time, IO OFF




______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1440500
Posted Tuesday, April 9, 2013 12:37 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
Also, if you are adding and deleting data, you really should create a clustered index on the table. As a heap it will just continue to grow and not reuse empty space allocated to the table.



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 #1440501
Posted Tuesday, April 9, 2013 12:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 1,909, Visits: 19,091
Lynn Pettis (4/9/2013)
Also, if you are adding and deleting data, you really should create a clustered index on the table. As a heap it will just continue to grow and not reuse empty space allocated to the table.


Thanks Lynn


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1440505
Posted Tuesday, April 9, 2013 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 6:57 AM
Points: 9, Visits: 49
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
Post #1440534
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse