how do I fine tune this query

Question

Post reply

how do I fine tune this query

Viewing 15 posts - 31 through 45 (of 48 total)

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 1

Do you want me to redefine the index using the "INCLUDE" clause ? Is that what you mean ?

Can you pass an example please of what you mean by a covering index ?

--Some thing like this ?

USE AdventureWorks2012;

GO

-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.

-- index key column is PostalCode and the nonkey columns are

-- AddressLine1, AddressLine2, City, and StateProvinceID.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON Person.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mw112009 (10/2/2015)
Do you want me to redefine the index using the "INCLUDE" clause ? Is that what you mean ?
Can you pass an example please of what you mean by a covering index ?
--Some thing like this ?
USE AdventureWorks2012;
GO
-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.
-- index key column is PostalCode and the nonkey columns are
-- AddressLine1, AddressLine2, City, and StateProvinceID.
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

No it's fine. See the edit to my previous post.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You might want to put some time into removing those DISTINCTs. In the second query the sort for the DISTINCT spills to disk because more rows pass through the operator than expected.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Not clear what you mean ?

Should I drop the PRIMARY key and CREATE AGAIN ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

mw112009 (10/2/2015)
Not clear what you mean ?
Should I drop the PRIMARY key and CREATE AGAIN ?

No. Since you want most of the rows and most of the columns from the table, the clustered index is the best choice.

There is a possibility of encouraging a merge join, if you could create a covering index on dbo.hh835DP hd with the two columns used in the join (in the same order as they appear in the ch table) and INCLUDE any other columns from the hd table referenced by the query. I don't think it would make a huge difference but it might be worth it to you.

How long does the top query of the two take to run?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Need some help here... I am new to the finetuning business..

I have attached the stored proc code...

The entire stored proc gets run under 3 seconds.. I am in the process of adding more code to it. But I thought I should fine tune it before I add more code..

I am not sure how to find how much time it takes for the first query. Can you pinpoint where to navigate to get that ?

Anyhow back to my original question was .. Why would SQL server have to scan an CLUSTERED INDEX without just doing a seek ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

mw112009 (10/2/2015)
Need some help here... I am new to the finetuning business..
I have attached the stored proc code...
The entire stored proc gets run under 3 seconds.. I am in the process of adding more code to it. But I thought I should fine tune it before I add more code..
I am not sure how to find how much time it takes for the first query. Can you pinpoint where to navigate to get that ?
Anyhow back to my original question was .. Why would SQL server have to scan an CLUSTERED INDEX without just doing a seek ?

Answering your original question: if more than a certain percentage of rows are required from a table, then the optimiser will choose a scan. The percentage is quite small, maybe 10%. In this particular case you want almost all of the rows.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

WayneS SSC Guru Points: 95460 More actions · Answer 8

mw112009 (10/2/2015)
Need some help here... I am new to the finetuning business..
I have attached the stored proc code...
The entire stored proc gets run under 3 seconds.. I am in the process of adding more code to it. But I thought I should fine tune it before I add more code..
I am not sure how to find how much time it takes for the first query. Can you pinpoint where to navigate to get that ?
Anyhow back to my original question was .. Why would SQL server have to scan an CLUSTERED INDEX without just doing a seek ?

Can you give us two additional pieces of information?

1. How many rows are in the HINSTP table?

2. Can you script out the CREATE TABLE statement for the HINSTP table and post all of it here (even all of the "SET" commands)?

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

mw112009 (10/2/2015)
Need some help here... I am new to the finetuning business..
I have attached the stored proc code...
The entire stored proc gets run under 3 seconds.. I am in the process of adding more code to it. But I thought I should fine tune it before I add more code..
I am not sure how to find how much time it takes for the first query. Can you pinpoint where to navigate to get that ?
Anyhow back to my original question was .. Why would SQL server have to scan an CLUSTERED INDEX without just doing a seek ?

Comment out the first query, run the stored procedure, capture the duration. Repeat with the second query, don't forget to uncomment out afterwards.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 10

USE [EDITransferICD10]

GO

/****** Object: Table [dbo].[HINSTP] Script Date: 10/2/2015 12:02:53 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[HINSTP](

[ADMDT] [decimal](8, 0) NOT NULL,

[FORMN] [varchar](8) NOT NULL,

[MBRNO] [varchar](11) NULL,

[PRVNO] [varchar](6) NULL,

[RFPRV] [varchar](6) NULL,

[AUVST] [decimal](8, 0) NULL,

[ACVST] [decimal](8, 0) NULL,

[ENRSN] [varchar](2) NULL,

[PLCSV] [varchar](2) NULL,

[PTYPE] [varchar](10) NULL,

[VNDNO] [varchar](10) NULL,

[APRV1] [varchar](6) NULL,

[DIAG1] [varchar](8) NULL,

[DIAG2] [varchar](8) NULL,

[MBAGE] [decimal](28, 1) NULL,

[MBSEX] [varchar](1) NULL,

[GRPID] [varchar](6) NULL,

[PLNCD] [varchar](3) NULL,

[DIAG3] [varchar](8) NULL,

[SPRC1] [varchar](8) NULL,

[SPRC2] [varchar](8) NULL,

[AUTHN] [varchar](9) NULL,

[CNTRC] [varchar](1) NULL,

[ATPHY] [varchar](6) NULL,

[IPACD] [varchar](2) NULL,

[DISDT] [decimal](8, 0) NULL,

[USRFL] [varchar](4) NULL,

[PRVAC] [varchar](20) NULL,

[SBGRC] [varchar](3) NULL,

[DIAG4] [varchar](8) NULL,

[DIAG5] [varchar](8) NULL,

[SPRC3] [varchar](8) NULL,

[SPRC4] [varchar](8) NULL,

[SPRC5] [varchar](8) NULL,

[DISST] [varchar](3) NULL,

[DRGCD] [varchar](5) NULL,

[MEMFL] [varchar](1) NULL,

[MMEDF] [varchar](1) NULL,

[PPROV] [varchar](1) NULL,

[PLNTP] [varchar](1) NULL,

[PPOEN] [varchar](3) NULL,

[COBTP] [varchar](2) NULL,

[OENDT] [decimal](8, 0) NULL,

[OROPR] [varchar](10) NULL,

[OBCHN] [varchar](6) NULL,

[LCHGD] [decimal](8, 0) NULL,

[LCH] [varchar](10) NULL,

[INUSE] [varchar](1) NULL,

[ONILD] [decimal](8, 0) NULL,

[ADDSQ] [decimal](8, 0) NULL,

[BRUSD] [varchar](6) NULL,

[BEABT] [decimal](9, 2) NULL,

[RIDER] [varchar](10) NULL,

[MSTCD] [varchar](8) NULL,

[DIAG6] [varchar](8) NULL,

[DIAG7] [varchar](8) NULL,

[DIAG8] [varchar](8) NULL,

[DIAG9] [varchar](8) NULL,

[DIAG10] [varchar](8) NULL,

[SPRC6] [varchar](8) NULL,

[UBBTP] [varchar](3) NULL,

[UBCVD] [decimal](8, 0) NULL,

[UBNCD] [decimal](8, 0) NULL,

[UBAHR] [decimal](8, 0) NULL,

[UBATP] [varchar](1) NULL,

[UBASC] [varchar](1) NULL,

[UBDHR] [decimal](8, 0) NULL,

[UBPST] [decimal](8, 0) NULL,

[UBCD1] [varchar](2) NULL,

[UBCD2] [varchar](2) NULL,

[UBCD3] [varchar](2) NULL,

[UBCD4] [varchar](2) NULL,

[UBCD5] [varchar](2) NULL,

[UBCD6] [varchar](2) NULL,

[UBCD7] [varchar](2) NULL,

[UBOC1] [varchar](2) NULL,

[UBOD1] [decimal](8, 0) NULL,

[UBOC2] [varchar](2) NULL,

[UBOD2] [decimal](8, 0) NULL,

[UBOC3] [varchar](2) NULL,

[UBOD3] [decimal](8, 0) NULL,

[UBOC4] [varchar](2) NULL,

[UBOD4] [decimal](8, 0) NULL,

[UBVC1] [varchar](2) NULL,

[UBVA1] [decimal](9, 2) NULL,

[UBVC2] [varchar](2) NULL,

[UBVA2] [decimal](9, 2) NULL,

[UBVC3] [varchar](2) NULL,

[UBVA3] [decimal](9, 2) NULL,

[UBTCH] [decimal](9, 2) NULL,

[UBNCH] [decimal](9, 2) NULL,

[UBBWT] [decimal](8, 0) NULL,

[UBALC] [decimal](8, 0) NULL,

[PRC1D] [decimal](8, 0) NULL,

[PRC2D] [decimal](8, 0) NULL,

[PRC3D] [decimal](8, 0) NULL,

[PRC4D] [decimal](8, 0) NULL,

[PRC5D] [decimal](8, 0) NULL,

[PRC6D] [decimal](8, 0) NULL,

[AUTHB] [varchar](18) NULL,

[AUTHC] [varchar](18) NULL,

[UBDFP] [decimal](9, 2) NULL,

[UBEAB] [decimal](9, 2) NULL,

[UBEAC] [decimal](9, 2) NULL,

[UBEFP] [decimal](9, 2) NULL,

[UBPRB] [decimal](9, 2) NULL,

[UBPR3] [decimal](9, 2) NULL,

[UBRIB] [varchar](1) NULL,

[UBRIC] [varchar](1) NULL,

[UBABB] [varchar](1) NULL,

[UBABC] [varchar](1) NULL,

[UBPRCA] [varchar](2) NULL,

[UBPRCC] [varchar](2) NULL,

[UBESCB] [varchar](1) NULL,

[UBESCC] [varchar](1) NULL,

[UBEMPB] [varchar](24) NULL,

[UBEMPC] [varchar](24) NULL,

[UBEML] [varchar](35) NULL,

[UBEMLB] [varchar](35) NULL,

[UBEMLC] [varchar](35) NULL,

[UBOC1B] [varchar](2) NULL,

[UBOD1B] [decimal](8, 0) NULL,

[UBOC2B] [varchar](2) NULL,

[UBOD2B] [decimal](8, 0) NULL,

[UBOC3B] [varchar](2) NULL,

[UBOD3B] [decimal](8, 0) NULL,

[UBOC4B] [varchar](2) NULL,

[UBOD4B] [decimal](8, 0) NULL,

[UBOSCB] [varchar](2) NULL,

[UBOSFB] [decimal](8, 0) NULL,

[UBOSTB] [decimal](8, 0) NULL,

[UBRPN] [varchar](40) NULL,

[UBRPA1] [varchar](40) NULL,

[UBRPA2] [varchar](40) NULL,

[UBRPA3] [varchar](40) NULL,

[UBRPA4] [varchar](40) NULL,

[USR1A] [varchar](12) NULL,

[USR1B] [varchar](13) NULL,

[FEDID] [varchar](9) NULL,

[USR2] [varchar](26) NULL,

[USR3A] [varchar](5) NULL,

[USR3B] [varchar](6) NULL,

[USRD3A] [varchar](14) NULL,

[USRD3B] [varchar](14) NULL,

[USRD3C] [varchar](14) NULL,

[USRD3D] [varchar](14) NULL,

[USRD3E] [varchar](14) NULL,

[USRD4] [varchar](27) NULL,

[USRD5] [varchar](2) NULL,

[USRD5B] [varchar](3) NULL,

[AADMT] [decimal](8, 0) NULL,

[MEDRC] [varchar](17) NULL,

[PAYRA] [varchar](25) NULL,

[PAYRB] [varchar](25) NULL,

[PAYRC] [varchar](25) NULL,

[PNBRA] [varchar](13) NULL,

[PNBRB] [varchar](13) NULL,

[PNBRC] [varchar](13) NULL,

[INSNA] [varchar](25) NULL,

[INSNB] [varchar](25) NULL,

[INSNC] [varchar](25) NULL,

[CERTA] [varchar](19) NULL,

[CERTB] [varchar](19) NULL,

[CERTC] [varchar](19) NULL,

[GRPNA] [varchar](14) NULL,

[GRPNB] [varchar](14) NULL,

[GRPNC] [varchar](14) NULL,

[GPNBA] [varchar](17) NULL,

[GPNBB] [varchar](17) NULL,

[GPNBC] [varchar](17) NULL,

[REMR1] [varchar](43) NULL,

[REMR2] [varchar](48) NULL,

[REMR3] [varchar](48) NULL,

[REMR4] [varchar](48) NULL,

[PRSIG] [varchar](1) NULL,

[NBIND] [varchar](1) NULL,

[MBIND] [varchar](1) NULL,

[ATCI1] [varchar](2) NULL,

[ATCI2] [varchar](2) NULL,

[ATCI3] [varchar](2) NULL,

[CLMNM] [varchar](30) NULL,

[CLDOB] [decimal](8, 0) NULL,

[UBCOD] [decimal](8, 0) NULL,

[UBLRD] [decimal](8, 0) NULL,

[UBMRN] [varchar](50) NULL,

[UBRC1] [varchar](4) NULL,

[UBRR1] [decimal](9, 2) NULL,

[UBRC2] [varchar](4) NULL,

[UBRR2] [decimal](9, 2) NULL,

[UBRC3] [varchar](4) NULL,

[UBRR3] [decimal](9, 2) NULL,

[UBRC4] [varchar](4) NULL,

[UBRR4] [decimal](9, 2) NULL,

[UBOSC] [varchar](2) NULL,

[UBOSF] [decimal](8, 0) NULL,

[UBOST] [decimal](8, 0) NULL,

[UBVC4] [varchar](2) NULL,

[UBVA4] [decimal](9, 2) NULL,

[UBVC5] [varchar](2) NULL,

[UBVA5] [decimal](9, 2) NULL,

[UBVC6] [varchar](2) NULL,

[UBVA6] [decimal](9, 2) NULL,

[UBVC7] [varchar](2) NULL,

[UBVA7] [decimal](9, 2) NULL,

[UBVC8] [varchar](2) NULL,

[UBVA8] [decimal](9, 2) NULL,

[UBVC9] [varchar](2) NULL,

[UBVA9] [decimal](9, 2) NULL,

[UBVCA] [varchar](2) NULL,

[UBVAA] [decimal](9, 2) NULL,

[UBVCB] [varchar](2) NULL,

[UBVAB] [decimal](9, 2) NULL,

[UBVCC] [varchar](2) NULL,

[UBVAC] [decimal](9, 2) NULL,

[UBPRP] [decimal](9, 2) NULL,

[UBEAD] [decimal](9, 2) NULL,

[UBRII] [varchar](1) NULL,

[UBABI] [varchar](1) NULL,

[UBPRC] [varchar](2) NULL,

[UBESC] [varchar](1) NULL,

[UBADX] [varchar](8) NULL,

[UBEDX] [varchar](8) NULL,

[UBPCM] [varchar](1) NULL,

[UBEMP] [varchar](24) NULL,

[UB1P1] [varchar](25) NULL,

[UB1P2] [varchar](32) NULL,

[UB2P1] [varchar](25) NULL,

[UB2P2] [varchar](32) NULL,

[UBILL] [decimal](8, 0) NULL,

[ATUSD] [varchar](1) NULL,

[HSUBID] [varchar](80) NULL,

[HPATC] [varchar](38) NULL,

[ESBNPI] [varchar](10) NULL,

[ESBTIN] [varchar](9) NULL,

[ESBMCI] [varchar](6) NULL,

[ESTNPI] [varchar](10) NULL,

[ESRNPI] [varchar](10) NULL,

[SBNPI] [varchar](10) NULL,

[SBTIN] [varchar](9) NULL,

[STNPI] [varchar](10) NULL,

[SRNPI] [varchar](10) NULL,

[BSCHST] [varchar](1) NULL,

[RSCHST] [varchar](1) NULL,

[ASCHST] [varchar](1) NULL,

[UBCD8] [varchar](2) NULL,

[UBCD9] [varchar](2) NULL,

[UBCDA] [varchar](2) NULL,

[UBCDB] [varchar](2) NULL,

[UBOC6A] [varchar](2) NULL,

[UBOC6B] [varchar](2) NULL,

[UBOF6A] [decimal](8, 0) NULL,

[UBOT6A] [decimal](8, 0) NULL,

[UBOF6B] [decimal](8, 0) NULL,

[UBOT6B] [decimal](8, 0) NULL,

[DIAG11] [varchar](8) NULL,

[DIAG12] [varchar](8) NULL,

[DIAG13] [varchar](8) NULL,

[DIAG14] [varchar](8) NULL,

[DIAG15] [varchar](8) NULL,

[DIAG16] [varchar](8) NULL,

[DIAG17] [varchar](8) NULL,

[DIAG18] [varchar](8) NULL,

[POA01] [varchar](1) NULL,

[POA02] [varchar](1) NULL,

[POA03] [varchar](1) NULL,

[POA04] [varchar](1) NULL,

[POA05] [varchar](1) NULL,

[POA06] [varchar](1) NULL,

[POA07] [varchar](1) NULL,

[POA08] [varchar](1) NULL,

[POA09] [varchar](1) NULL,

[POA10] [varchar](1) NULL,

[POA11] [varchar](1) NULL,

[POA12] [varchar](1) NULL,

[POA13] [varchar](1) NULL,

[POA14] [varchar](1) NULL,

[POA15] [varchar](1) NULL,

[POA16] [varchar](1) NULL,

[POA17] [varchar](1) NULL,

[POA18] [varchar](1) NULL,

[UBADX1] [varchar](8) NULL,

[UBADX2] [varchar](8) NULL,

[UBADX3] [varchar](8) NULL,

[POAPR1] [varchar](1) NULL,

[POAPR2] [varchar](1) NULL,

[POAPR3] [varchar](1) NULL,

[UBEDX2] [varchar](8) NULL,

[UBEDX3] [varchar](8) NULL,

[POAEC1] [varchar](1) NULL,

[POAEC2] [varchar](1) NULL,

[POAEC3] [varchar](1) NULL,

[UBDRG] [varchar](5) NULL,

[SVCNAM] [varchar](75) NULL,

[SVCAD1] [varchar](55) NULL,

[SVCAD2] [varchar](55) NULL,

[SVCCTY] [varchar](30) NULL,

[SVCSTE] [varchar](2) NULL,

[SVCZIP] [varchar](9) NULL,

[DGPRC] [varchar](2) NULL,

[AGPRC] [varchar](2) NULL,

[AODISP] [varchar](2) NULL,

[ECNFL] [varchar](1) NULL,

[OVRCL] [varchar](1) NULL,

[OVRST] [varchar](1) NULL,

[SYNCID] [decimal](8, 0) NULL,

[MHPDTE] [decimal](8, 0) NULL,

[MHPTME] [decimal](8, 0) NULL,

[MHPUSR] [varchar](10) NULL,

[MHPJOB] [varchar](10) NULL,

[MHPPGM] [varchar](10) NULL,

[PAYNME] [varchar](60) NULL,

[REDRG] [varchar](5) NULL,

[ALDRG] [varchar](5) NULL,

[ICDVER] [char](1) NULL,

[EDI835Exclude] [tinyint] NOT NULL,

CONSTRAINT [PK_HINSTP] PRIMARY KEY CLUSTERED

(

[ADMDT] ASC,

[FORMN] ASC

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

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

mw_sql_developer SSCoach Points: 19445 More actions · Answer 11

Time taken by first query = Anywhere from 13 to 52 milli seconds. ( I hit the execute button a several times )

Also the hinstp has only 479 records... Hmmm That may be the reason SQL Server did a scan...

I have heard people say when the record count is less than x then SQL server will not use the index ( Is that right ) ?

So the issue is not clearly with the first query... It is the second one that takes. time.

However I am concerned why the index was scanned ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

mw112009 (10/2/2015)
Time taken by first query = Anywhere from 13 to 52 milli seconds. ( I hit the execute button a several times )
Also the hinstp has only 479 records... Hmmm That may be the reason SQL Server did a scan...
I have heard people say when the record count is less than x then SQL server will not use the index ( Is that right ) ?
So the issue is not clearly with the first query... It is the second one that takes. time.
However I am concerned why the index was scanned ?

I've already answered why the index was scanned. If you want one row, you do a seek. If you want 450 rows of a 479 row table, you don't do 479 seeks, you do a scan.

The second query IIRC returns 10 times more rows than the first - and isn't that the one with the spilling sort?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mw_sql_developer SSCoach Points: 19445 More actions · Answer 13

Now sure what spilling sort means ?

But yes the second returns 4719 rows...

mw_sql_developer SSCoach Points: 19445 More actions · Answer 14

Ahh I found a way to reduce the time...

1.) Remove the DISTINCT ... Why ? The UNION operator always will return the distinct rows. I ran a little script and managed to convince myself.

The last SELECT stmt returns 10 rows ( it is the distinct ones and is sorted as well )

--------------

create table #t1( name varchar(100), addr varchar(100) );

create table #t2( name varchar(100), addr varchar(100) ) ;

INSERT INTO #t1( name, addr )

Select 'xyzzz', 'kasjdlas'

UNION

Select 'dee','asd'

UNION

Select 'abc','x'

UNION

Select 'ckjhik','jj'

UNION

Select 'ckjhik','jj'

UNION

Select 'ckjhik','jj'

UNION

Select '0asldkjasd','fghh'

INSERT INTO #t2( name, addr )

Select 'xyzzz','tgrt'

UNION

Select 'xyzzz','tgrt'

UNION

Select 'xyzzz','tgrt'

UNION

Select 'dee','766'

UNION

Select 'abc','gfhfgh'

UNION

Select 'ckjhik','hnhhh'

UNION

Select '0asldkjasd','xxx'

Select name, addr from #t1

UNION

Select name, addr from #t2

WayneS SSC Guru Points: 95460 More actions · Answer 15

mw112009 (10/2/2015)
Time taken by first query = Anywhere from 13 to 52 milli seconds. ( I hit the execute button a several times )
Also the hinstp has only 479 records... Hmmm That may be the reason SQL Server did a scan...
I have heard people say when the record count is less than x then SQL server will not use the index ( Is that right ) ?
So the issue is not clearly with the first query... It is the second one that takes. time.
However I am concerned why the index was scanned ?

So, you're returning a lot of columns from all of the rows. To use the smaller index, it would have to then go to the clustered index (via a key lookup on each row). So, which do you think would be faster? Read the two pages for the index (one for the leaf level, one for the root level), scan through this index, and do 479 seeks into the clustered index? Or just read the 240 pages for this table (plus 1 for the index root level page), and just scan that? Yeah, those seeks for the key lookup will get very expensive, very fast. Purely for educational purposes, you could add a hint to the query to force it to use the smaller index to see what it does to both the query plan and the time for the query.

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