SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help optimizing a query


Need help optimizing a query

Author
Message
jrestuccio
jrestuccio
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 51
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21585 Visits: 7660
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
jrestuccio
jrestuccio
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 51
Here you go, I tried to include all relevant information.
Again thanks for helping!

-Josh
Attachments
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98498 Visits: 38996
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

Cool
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)
jrestuccio
jrestuccio
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 51
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65070 Visits: 17979
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 Modens 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)
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12604 Visits: 37673
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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98498 Visits: 38996
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.

Cool
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)
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12604 Visits: 37673
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

jrestuccio
jrestuccio
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 51
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search