select query taking more time (execution time)

Question

Post reply

select query taking more time (execution time)

vinayak_mohan

Mr or Mrs. 500

Points: 573
More actions
October 20, 2010 at 8:45 am

#239996

Hi All,
We have a normal select query on a table which takes around 16 mins to execute. The table has 30 lakh records and the query returns almost 20 lakh records. We have used ltrim and cast to concate the string and integer values to generate a single filed record. Pleas can anybody suggest on how to reduce the execution time of the select query. I have pasted the query below.SELECT
'01'
+ Replicate('0',(9-Len(Prod_No)))+ Rtrim (cast(Prod_No as varchar(9)))
+ Replicate('0',(8-Len(Order_Number)))+ Rtrim (cast(Order_Number as varchar(8)))
+ Replicate('0',(5-Len(Store_Number)))+ Rtrim (cast(Store_Number as varchar(5)))
FROM dbo.Table1 (nolock)
WHERE Deleted_Ind = 0
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

Geoff A SSChampion Points: 11511 More actions · Answer 1

add an index. (if its not there)

USE [YourDataBaseName]

GO

CREATE NONCLUSTERED INDEX [IX_Deleted_Ind ] ON [dbo].[Table1]

(

[Deleted_Ind] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Geoff A (10/20/2010)
add an index. (if its not there)
USE [YourDataBaseName]
GO
CREATE NONCLUSTERED INDEX [IX_Deleted_Ind ] ON [dbo].[Table1]
(
[Deleted_Ind] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The query would have to return around 0.3% of the records of the table to garantee the use of the record.

Since it's returning 66% of the table that index is less than useless.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

There's no way this is the whole query. I have a small laptop here and returning 1M records takes only seconds.

That means that your query is either way more complex than what you show or your server is severely stressed (and or under severe locking).

vinayak_mohan Mr or Mrs. 500 Points: 573 More actions · Answer 4

Hi Guys,

Thanks for the suggestion, but regarding the complexity of the query it still puzzling me as its a blanket select statement.

I will add index to the table and let you know the performance of the query. But there is one problem here. I cannot add index directly to the table as it is a production database. So i have to first populate the Table1 contents to a temporary table and then add index to the temp table and later should try extracting the data.

I tried removing the cast and trim options but it did not give me good results only saving 45 seconds of whole 16 mins.

Any other suggestions is most welcome.

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Out of curiosity...are you executing this on your machine locally connected to a remote server and pulling results over the WAN?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Derrick Smith (10/22/2010)
Out of curiosity...are you executing this on your machine locally connected to a remote server and pulling results over the WAN?

I second that. What happens if you run this straight from the server (terminal server on the machine and run from SSMS)?

If it's still slow then, you're problem is with the server itself, if it's fast then your network or your connection on the network is the problem.

tonydp Valued Member Points: 70 More actions · Answer 7

Could you try the query (remotely) without having any function calls but with the 'where' condition in tact, for example:

SELECT '01', Prod_No

FROM dbo.Table1 (nolock)

WHERE Deleted_Ind = 0

Is the performance still a problem?

There are some questions about the structure of the query. You are casting each of the columns to varchar(), what data types are the columns to start with?

You are casting columns to varchar() but using the len() of the original column definition (for instance, what is the original length of the Prod_No column, greater than 9?). If the second parameter of the Replicate function call goes negative does this affect results or performance?

A simplier means of providing leading zeroes may be as follows

right('000000000'+rtrim(Prod_No),9) (assuming Prod_No is of a string type)

this removes some function calls.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

vinayak_mohan (10/20/2010)
Hi All,
We have a normal select query on a table which takes around 16 mins to execute. The table has 30 lakh records and the query returns almost 20 lakh records. We have used ltrim and cast to concate the string and integer values to generate a single filed record. Pleas can anybody suggest on how to reduce the execution time of the select query. I have pasted the query below.SELECT
'01'
+ Replicate('0',(9-Len(Prod_No)))+ Rtrim (cast(Prod_No as varchar(9)))
+ Replicate('0',(8-Len(Order_Number)))+ Rtrim (cast(Order_Number as varchar(8)))
+ Replicate('0',(5-Len(Store_Number)))+ Rtrim (cast(Store_Number as varchar(5)))
FROM dbo.Table1 (nolock)
WHERE Deleted_Ind = 0
Thanks in advance

I agree with Tony. The calculations can be greatly simplified. For example, right now you're calculating 3 totally unnecessary Replicate's, Len's, and Rtrim's for each row returned.

Also, if Deleted_Ind is a BIT datatype, adding an index to it won't help much at all unless you convert the "0" to a bit to keep the datatype precedence between a BIT and and INT from converting the BIT column to INT before doing the filtering. So.... something like the following should work much more effeciently. If you want tested code, then you'll need to provide some data. Don't assume you know how to do that... see the first link in my signature below for the best way for you to help us help you.

Here's the code...

SELECT '01'

+ Right('000000000'+cast(Prod_No as varchar(9)),9)

+ Right('000000000'+cast(Order_Number as varchar(8)),8)

+ Right('000000000'+cast(Store_Number as varchar(5)),5)

FROM dbo.Table1 (nolock)

WHERE Deleted_Ind = CAST(0 AS BIT)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Also remember that returning 20 lakh (2 million rows) is going to take a while even if it's a straight select. If you're returning that many rows to the display, I'd have to ask "WHY"? Who do you think is going to read 20 lakh rows? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)