Using Parameters in a Query is Killing Performance

Question

Post reply

Using Parameters in a Query is Killing Performance

Viewing 15 posts - 16 through 30 (of 58 total)

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

Loner SSC-Insane Points: 21279 More actions · Answer 1

We purchase the software for our company and we cannot just update the database and create index.

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

Fixing this will require either changing the query or changing the index. Can you do either?

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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

Loner (2/18/2016)
We purchase the software for our company and we cannot just update the database and create index.

I'll ask again...

Jeff Moden (2/17/2016)
Loner (2/17/2016)
Ok, I give up, someone please help! We have a query that runs just fine with hard coded values but once we swap them out for parameters the query runs with no end in sight.
This version consistently returns 8,429 rows in less than 2 seconds:
SELECT username, firstname, lastname
FROM Users
WHERE UpdateDt BETWEEN '2/11/2016' AND '2/12/2016';
This version will run well over an hours and not return data:
DECLARE @Dt1 DATETIME, @dt2 DATETIME;
SET @Dt1 = '2/11/2016';
SET @dt2 = '2/12/2016';
SELECT username, firstname, lastname
FROM Users
WHERE UpdateDt BETWEEN @Dt1 AND @dt2;
The UpdateDt column in the Users table is DATETIME so I wouldn't expect any datatype conversion issues.
Any theories on what could be causing this?
So what's the CREATE TABLE for this table look like, what are the indexes on it, and how many rows does it have?

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

Phil Parkin SSC Guru Points: 246874 More actions · Answer 4

Sergiy (2/17/2016)
Alan.B (2/17/2016)
Sergiy (2/17/2016)
Make the PK on UserID non-clustered and create a clustered index on UpdateDt.
Unless UpdateDt is not unique. Then perhaps a clustered index on composite key consisting of UpdateDt and UserID (in that order).
Clustered index does not have to be unique.
Which does not mean I would object your suggestion.

As I'm sure you know, the clustered index actually does have to be unique. If the user-specified columns are not unique in themselves, SQL Server will add a Uniquifier to make them so.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Loner...

Using SSMS, expand the table list, find the Users table, right click on it and chose Script table as > CREATE to Query window. Then copy the table script and post it for us. That will help us help you. Then run this and provide us the results:

USE <yourdbname>

GO

SELECT COUNT(*)

FROM users

GO

That's what you've been asked for....and gives everyone a good idea of what you are working with.

-SQLBill

Loner SSC-Insane Points: 21279 More actions · Answer 6

Loner

SSC-Insane

Points: 21279

February 18, 2016 at 2:32 pm

#1859701

The table has 2357880092 number of records.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Loner (2/18/2016)
The table has 2357880092 number of records.

Thanks. Please provide a CREATE TABLE statement including all indexes and constraints.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Phil Parkin (2/18/2016)
As I'm sure you know, the clustered index actually does have to be unique. If the user-specified columns are not unique in themselves, SQL Server will add a Uniquifier to make them so.

Yes, I know. I quoted that article from MSDN on this forum last week.

🙂

But it has nothing to do with CREATE INDEX statement.

We can define any index as clustered.

SQL Server will fill up the gaps in its uniqueness behind the scenes, where needed.

_____________
Code for TallyGenerator

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

Loner (2/18/2016)
The table has 2357880092 number of records.

I still need the CREATE TABLE statement and the indexes for the table so that I can build a large test table to test for a solution for you.

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Loner: It's a long topic already so I may have overlooked something. Did you already try the OPTION (RECOMPILE) hint that Scott suggested? Did it help?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Loner SSC-Insane Points: 21279 More actions · Answer 11

Yes I put in Option recompile and still ran a long time.

Loner SSC-Insane Points: 21279 More actions · Answer 12

I just look at the table, it has 12 FK and 1 PK and 11 non unique non cluster index.

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

Can you post execution plans of the fast and the slow versions please? Actual plans if possible.

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

Phil Parkin SSC Guru Points: 246874 More actions · Answer 14

Loner (2/19/2016)
I just look at the table, it has 12 FK and 1 PK and 11 non unique non cluster index.

Why are you refusing to provide the information which the people here are requesting? There are some extremely proficient people contributing here, yet you are asking them to work blindfolded in a darkened room.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 15

Loner (2/19/2016)
I just look at the table, it has 12 FK and 1 PK and 11 non unique non cluster index.

I don't need the FK's. Just the CREATE TABLE and the indexes. If you're scripting options are setup correctly, the indexes will be generated at the same time you script out the table.

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