Need Expert advice on my sql query for speed improvement

Question

Post reply

Need Expert advice on my sql query for speed improvement

Agile

Ten Centuries

Points: 1153
More actions
June 24, 2010 at 7:15 pm

#74070

This query reads on 2746984 rows. When I run this , it takes 14Seconds for the output.
Is this normal or is there other way to speed up the query?
And please do inform me even other details like readability of my script or correct usage of datatype etc.
SELECT UID1FirstName,
UID1LastName,
CONVERT(varchar(10),dtdate,101) AS Adate,
MIN(dtdate) AS ATime,
DATEPART(dw,dtdate) AS DOW,
TmsR=CASE
WHEN Reader=6 OR Reader=7 THEN
'I'
END,
AorB=CASE
WHEN Reader=6 OR Reader=7 THEN
'A'
END
FROM Sec.dbo.Log_Transactions AS L INNER JOIN TMS.dbo.Users AS U
ON L.UID1FirstName=U.EmpNo
WHERE (Event=2000) AND
(Reader =6 OR Reader=7) AND
CAST(CONVERT(varchar(10),dtdate,101) AS datetime) = CAST(CONVERT(varchar(10),GETDATE(),101) AS datetime)
GROUP BY UID1FirstName,
UID1LastName,
CONVERT(varchar(10),dtdate,101),
DATEPART(dw,dtdate),
Reader
ORDER BY UID1LastName ASC
--Table that has 2746984 rows
--Sec
CREATE TABLE [dbo].[Log_Transactions] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerID] [int] NOT NULL ,
[dtDate] [datetime] NOT NULL ,
[Event] [int] NOT NULL ,
[Reader] [tinyint] NOT NULL ,
[UID1FirstName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UID1LastName] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--Other table that has 1888 rows
--TMS
CREATE TABLE [dbo].[Users] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EmpName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Thanks in advance!
Thanks,
Morris

Viewing 15 posts - 1 through 15 (of 16 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Returning 2.75 million rows in 14 seconds to the screen is pretty good.

That being said, I see one area where it might be improved upon.

When a column in a table is wrapped in a function in a where clause or join condition, indexes on that column can't be used. So this line of your code:CAST(CONVERT(varchar(10),dtdate,101) AS datetime) = CAST(CONVERT(varchar(10),GETDATE(),101) AS datetime) won't use any indexes.

What you need to do is:

declare @StartDate datetime,

@EndDate datetime

set @StartDate = DateAdd(d, DateDiff(d, 0, GetDate()), 0) -- get the current day only

set @EndDate = DateAdd(d, 1, @StartDate) -- get the next day

select ...

where dtDate >= @StartDate

AND dtDate < @EndDate

Notice how:

1. dtDate is GREATER THAN OR EQUAL TO @StartDate - this will get anything of today's date from 00:00:00.000 to 23:59:59.997

2. dtDate is LESS THAN @EndDate - this will get anything before tomorrow's date

3. since neither expression has dtDate in a function, the query can use an index on dtDate

If you had used between, you would potentially get anything of tomorrow's date at 00:00:00.000

and when you upgrade to 2008 or beyond, this code will still work if you change these datatypes to datetime2, which has a precision of microseconds instead of 3 milliseconds. So if the datatype were to be changed to datetime2, you would be able to get a date with a time of 23:59:59.9999999

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Agile Ten Centuries Points: 1153 More actions · Answer 2

Thanks for the respond, I really appreciate your advice, I will follow all of your advice.

Thanks again

Morris

Thanks,
Morris

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If it's still too slow after making those changes, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Why are you returning millions of rows? Where are the rows going?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Agile Ten Centuries Points: 1153 More actions · Answer 4

Sorry for the misunderstanding, What I mean is that the table has 2million records on it. I only need to query or get a specific data on that 2milllion records. By estimate the ouput is not more than 2000 records.

But I still attached my execution plan and table definition, in case there's other issue.

--This table has 2 million records (Sec)

CREATE TABLE [dbo].[Log_Transactions] (

[LogID] [int] IDENTITY (1, 1) NOT NULL ,

[ServerID] [int] NOT NULL ,

[dtDate] [datetime] NOT NULL ,

[Event] [int] NOT NULL ,

[Reader] [tinyint] NOT NULL ,

[UID1FirstName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UID1LastName] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

--This table has 1888 records (TMS)

CREATE TABLE [dbo].[Users] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[EmpNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[EmpName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Department] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

thanks

Morris

Thanks,
Morris

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Use Wayne's advice for WHERE clause and make sure yout table is built like this:

CREATE TABLE [dbo].[Log_Transactions] (

[LogID] [int] IDENTITY (1, 1) NOT NULL ,

[ServerID] [int] NOT NULL ,

[dtDate] [datetime] NOT NULL ,

[Event] [int] NOT NULL ,

[Reader] [tinyint] NOT NULL ,

[UID1FirstName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UID1LastName] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

PRIMARY KEY NONCLUSTERED (LogID)

)

CREATE CLUSTERED INDEX CX_Log_Transactions_dtDate ON [dbo].[Log_Transactions] (dtDate, Event)

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Sergiy (6/26/2010)
Use Wayne's advice for WHERE clause and make sure yout table is built like this:
CREATE TABLE [dbo].[Log_Transactions] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerID] [int] NOT NULL ,
[dtDate] [datetime] NOT NULL ,
[Event] [int] NOT NULL ,
[Reader] [tinyint] NOT NULL ,
[UID1FirstName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UID1LastName] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY NONCLUSTERED (LogID)
)
CREATE CLUSTERED INDEX CX_Log_Transactions_dtDate ON [dbo].[Log_Transactions] (dtDate, Event)

Not that either Sergiy or Wayne need a leg up but I'll definitely second the recommendations above. Unless there's something missing that I'm not aware of, they will probably drop the overall time for your code from 14 seconds to subsecond times.

--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)

Agile Ten Centuries Points: 1153 More actions · Answer 7

Thanks guys! I think it's too much if I ask again what are the meaning of the items that you inserted. I'll just study them by my self and maybe if I can't understand some things, I'll try to post another topic.

PRIMARY KEY NONCLUSTERED (LogID)

)

CREATE CLUSTERED INDEX CX_Log_Transactions_dtDate ON [dbo].[Log_Transactions] (dtDate, Event)

Thanks again guys!

Morris

Thanks,
Morris

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

Agile (6/27/2010)
Thanks guys! I think it's too much if I ask again what are the meaning of the items that you inserted. I'll just study them by my self and maybe if I can't understand some things, I'll try to post another topic.
PRIMARY KEY NONCLUSTERED (LogID)
)
CREATE CLUSTERED INDEX CX_Log_Transactions_dtDate ON [dbo].[Log_Transactions] (dtDate, Event)
Thanks again guys!
Morris

You should still take the time to look them up but it's easy... table isn't really a table without a PK or a clustered index. In this case, we need the clustered index (which physically sorts the data in the table) to get the best speed on the date/event lookup so the PK was explicitly declared as a "non-clustered" index... there can only be one clustered index.

--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)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Jeff Moden (6/27/2010)(which ~~physically~~ sorts the data in the table)

Logically , i think you mean Jeff 😉

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

Dave Ballantyne (6/28/2010)
Jeff Moden (6/27/2010)(which ~~physically~~ sorts the data in the table)
Logically , i think you mean Jeff 😉

Nope... I meant "physically".

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

A clustered index does not ensure that the data is stored physically in the order of the index. Only if there's 0 fragmentation of index and file does the logical sort order (which the index does set) match the physical order that data is stored on disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

I know *we* are on the same page here. Personally i dislike the word physical as that would imply that on the hardware, page 2 would follow page 1 up to page X in a perfect order.

Although this *may* be true with a freshly built clustered index , on a page split the following pages obviously don't get shuffled forward to make space and maintain the 'physical' order.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Dave Ballantyne (6/28/2010)
I know *we* are on the same page here. Personally i dislike the word physical as that would imply that on the hardware, page 2 would follow page 1 up to page X in a perfect order.
Although this *may* be true with a freshly built clustered index , on a page split the following pages obviously don't get shuffled forward to make space and maintain the 'physical' order.

You're correct about the page splits. They're going to be out of order and even on a freshly rebuilt clustered index, there may be interleaving of pages with other table's data. Still and even with interleaving (page splits not withstanding), the pages are in physical order and the data on each page is also in physical order.

But I also understand where you're coming from and why the words "physical order" might not sit just right with some folks. And, I agree... we're on the same page here. I just don't take exception to the word as much as some do especially since BOL uses the word "physical" to describe it, as well.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Jeff Moden (6/28/2010)
...and the data on each page is also in physical order.

Not necessarily. Rows on a page don't have to be in any specific order, SQL puts them where there's space. There's a slot array at the end of the page that defines what the order of the rows is supposed to be. http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Proof-that-records-are-not-always-physically-stored-in-index-key-order.aspx

I personally prefer to avoid the word 'physical' here, as it leads to misunderstandings. eg the definition for logical fragmentation is the % of pages where the physical order (defined by the Page No) doesn't match the logical order, so if someone believes that indexes are always stored in physical order, what is their understanding of fragmentation?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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