Help With Query

Question

Help With Query

abendigeri

SSC-Addicted

Points: 433
More actions
January 29, 2016 at 10:40 am

#311145

Dear SQL Guru's.
We have a scenario.
We have a table where daily data is loaded. This table has 700 million rows.
I know this table have a missing index, but not able to create due to large data in this table.. It eats all out tempdb and log space. I cannot attempt to do it as its a prod environment.
Users run below query which is creating a problem and need to be optimized.
I have been trying hard to rewrite it, but due to my skills, i am having a hard time to optimize it. Please help.
I have tried to use CASE whereever required, but it hasn't helped..
Please help or suggest.... Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

According to the table definition, TestDTTM is already a datetime value, so the lowest-hanging fruit is to change the WHERE clause use the datetime values directly instead of converting to a string, replacing some values, and comparing to a different string.

That's just preventing you from using an index on TestDTTM, if one exists, and spending extra CPU as well.

Try replacing this:

AND REPLACE(REPLACE(CONVERT(VARCHAR(10),TestDTTM,120),':',''),'-','') >= '20160119'

AND REPLACE(REPLACE(CONVERT(VARCHAR(10),TestDTTM,120),':',''),'-','') < '20160124'

with this:

AND TestDTTM>='20160119' AND TestDTTM<'20160124'.

The particular index you posted DDL for wouldn't really help with this query, since the leading column in the key is not used to filter the results of the query at all.

Cheers!

abendigeri SSC-Addicted Points: 433 More actions · Answer 2

Jacob,

Thanks and appreciate your help.

I have tried with eliminating the REPLACE function, but not much helpful.

Reason behind using REPLACE is Some are capturing Test Date time in different format to comply with them I had to Convert TestDTTM to that format ..

Thanks

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

abendigeri (1/29/2016)
Jacob,
Thanks and appreciate your help.
I have tried with eliminating the REPLACE function, but not much helpful.
Reason behind using REPLACE is Some are capturing Test Date time in different format to comply with them I had to Convert TestDTTM to that format ..
Thanks

If the column is of the datetime datatype, then you shouldn't need to correct any formatting. You'd only have to worry about that if you were storing the datetimes as strings.

The big problem with doing the conversion is that it prevents you from using an index on TestDTTM (the extra CPU burned isn't great, but isn't the biggest problem). If you don't have a suitable index, then this query will just have to scan the entire table, and the room for performance gains from changing the query itself will be minimal.

Cheers!

abendigeri SSC-Addicted Points: 433 More actions · Answer 4

Got it Mate. I'll try to redesign this process.

Thanks for your help

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Wild idea:

If you can't create adequate indexes on this table, try creating a new table with the same structure but proper indexes (probably clustering on TestDTTM) and load the data. You'll need to do incremental loads, to prevent the problems with the log size, probably using a different tool such as SSIS or bcp.

Once both tables are synced, you drop the original and rename the new one, you might need to work with dependent objects as well.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2