which one is faster - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

which one is faster

Author

Topic

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2012-04-12 : 01:53:56

hi to all i have a confusion about a query
i have to get number of records which is passed in SP and final query was build and execute so my confusion is which one is faster from following query


--Query 1

SELECT [ID],
       [A_PickList],
       CONVERT(NVARCHAR(MAX), [MyDate], 103) AS [MyDate],
       [Myid],
       [Name],
       [Grade],
       [Pick2]
FROM   (SELECT Row_number() OVER (ORDER BY mr1x_tb.mr1x_entity_id ASC) AS [This_Table_RoW_Number],
               mr1x_tb.mr1x_entity_id AS [ID],
               mr1x_tb.mr1x_a_picklist AS [A_PickList],
               mr1x_tb.mr1x_mydate AS [MyDate],
               mr1x_tb.mr1x_myid AS [Myid],
               mr1x_tb.mr1x_name AS [Name],
               mr1x_grade_mr1x_tb_s3s_picklist.picklistvalue AS [Grade],
               mr1x_pick2_mr1x_tb_s3s_picklist.picklistvalue AS [Pick2]
        FROM   mr1x_tb
               LEFT OUTER JOIN s3s_picklist AS mr1x_grade_mr1x_tb_s3s_picklist ON mr1x_grade_mr1x_tb_s3s_picklist.picklistid = mr1x_tb.mr1x_grade
               LEFT OUTER JOIN s3s_picklist AS mr1x_pick2_mr1x_tb_s3s_picklist ON mr1x_pick2_mr1x_tb_s3s_picklist.picklistid = mr1x_tb.mr1x_pick2)
       alias_a
WHERE  alias_a.this_table_row_number BETWEEN 0 AND 5 

--Query 2

SELECT TOP(5)[ID],
             [A_PickList],
             CONVERT(NVARCHAR(MAX), [MyDate], 103) AS [MyDate],
             [Myid],
             [Name],
             [Grade],
             [Pick2]
FROM   (SELECT mr1x_tb.mr1x_entity_id  AS [ID],
               mr1x_tb.mr1x_a_picklist AS [A_PickList],
               mr1x_tb.mr1x_mydate AS [MyDate],
               mr1x_tb.mr1x_myid AS [Myid],
               mr1x_tb.mr1x_name AS [Name],
               mr1x_grade_mr1x_tb_s3s_picklist.picklistvalue AS [Grade],
               mr1x_pick2_mr1x_tb_s3s_picklist.picklistvalue AS [Pick2]
        FROM   mr1x_tb
               LEFT OUTER JOIN s3s_picklist AS mr1x_grade_mr1x_tb_s3s_picklist ON mr1x_grade_mr1x_tb_s3s_picklist.picklistid = mr1x_tb.mr1x_grade
               LEFT OUTER JOIN s3s_picklist AS mr1x_pick2_mr1x_tb_s3s_picklist ON mr1x_pick2_mr1x_tb_s3s_picklist.picklistid = mr1x_tb.mr1x_pick2)
       alias_a

-------------
Chirag
India
Sr. Web Engineer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-12 : 03:09:06

I would say that the query that takes least amount of time is the fastest.
If Query1 takes 5 seconds and Query2 takes 10 seconds, then Query1 is the fastest.

But this is only my [educated] guess.

N 56°04'39.26"
E 12°55'05.63"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-12 : 06:56:42

Not that this is any faster but this:

CONVERT(NVARCHAR(MAX), [MyDate], 103) AS [MyDate],

should be:

CONVERT(CHAR(10), [MyDate], 103) AS [MyDate],

1. Dates do not contain extended national characters, so Nvarchar is unnecessary.
2. 103 always produces 10 characters of output, so varchar is unnecessary, and varchar(max) is just plain silly and wasteful.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-12 : 07:12:46

Don't you need to add an order by clause in the second query?

Once you add the order by clause, intuitively I don't think there will be any significant difference between the two. But my intuition has led me to places that I don't want to be in (such as crashed servers and convoluted queries). If you must find out which would perform better, compare the query plans and/or run some test cases.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-12 : 08:09:05

quote:
Originally posted by SwePeso

I would say that the query that takes least amount of time is the fastest.
If Query1 takes 5 seconds and Query2 takes 10 seconds, then Query1 is the fastest.

But this is only my [educated] guess.

N 56°04'39.26"
E 12°55'05.63"

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources