Yes, "Order By ISNULL(Ind_Priority, DBO.MaxInt())" was to put the nulls at the end (the select statement does a Top 1).

Sorry for wasting everyone's time with this....(about it only being 15...

Thanks everyone so far....

I've tried this:

Create NonClustered Index idxNC_CustIdentType On [dbo].[Cust_Ident_Type]

(

Effective_DT Asc,

End_DT Asc

) Include (Ind_Priority)

And this:

Create NonClustered Index idxNC_CustIdentType On [dbo].[Cust_Ident_Type]

(

Effective_DT Asc,

End_DT Asc,

...

Rob Schripsema (9/28/2011)

---

I'll venture that the 'OR' clause in the join statement is the issue here. You're looking for values that are either NULL (at one end of the index...

What I've always done when trying to create indexes (whether wrong or not), is go by these rules [for nonclustered indexes]:

For joined tables, the first column is what is used...

Appreciate the quick reply Sean, I'll read through that linked article also...

Would I see any benefit creating a connection at Application_Start, or would that even be of use since connection...

Bringing back this post from the dead.

We are experiencing some very random slowness with one of our stored procedure executions from our web server. We wrote a dummy GUI program...

LutzM (9/16/2011)

---

I'm wondering why the code is written in a way that even if the first search block (match on all fields passed) returns a single CustomerNumber, the code will...

Oh, it just gets the primary key value for the 'text' version of something.

CustIdentType is the table name, get the primary key value for 'individual'. (Edit: woops, I mean 'DriversLicense'...

Here's the portion of code that calls the stored procedure:

USE [IARTS]

GO

Set NoCount On

Declare pwdCursor Cursor Local Forward_Only For

SelectCustomerNumber,

SSN,

DLN,

LastName,

FirstName,

MiddleName,

Suffix,

DOB

FrompwdList

WhereNewCustomerNumber IS NULL

Declare@oldCustomerNumber Int,

@SSN varchar(9),

@DLN varchar(15),

@LastName varchar(40),

@FirstName varchar(40),

@MiddleName varchar(40),

@Suffix varchar(5),

@DOB varchar(8),

@newCustomerNumber Int,

@output varchar(max)

Open...

Ah, after some more googling, apparently you can't run dynamic sql inside a function...darn.

Thanks again Lutz!

Another completely different question for you if you don't mind answering.

I have a stored procedure that needs to do a customer lookup based on some values, it's currently...

Yes, it has been, Lutz 🙂

Those darn CTE's just haven't clicked in my brain yet, but i'm getting better.

So it appears as though you don't need to have the Group...

Ok, I kind of figured out a way, but I don't like it.

I did this:

Select CustomerNumber, IssuedDate

From

(

Select Top 100000 CustomerNumber, IssuedDate, Rank() Over (Partition By CustomerNumber...

To be more exact in what I'm working on....

We still have 1 stored procedure that is not performing well (just noticed it). It's a straight-forward procedure, just a select with...

We upgraded to SQL Server 2008 a couple weeks ago and we flagged all stored procedures to recompile with the 'exec sp_recompile' command and it didn't help anything. Also, we...