Simple Query - long time

Question

Simple Query - long time

Tara-1044200

SSCoach

Points: 15951
More actions
July 20, 2009 at 3:11 pm

#208423

I have this simple query for 10 records and it is taking more than an hour, not sure where to look into for exact reason.
select top 10* from vwemp whwere empcode='BHK09287345'
where clause on other fields comes back in seconds but only this field taking long. This filed is not part of the index.
check the showplan attached.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, please read Gail's article on asking for help with performance tuning, it is the second article i reference below in my signature block.

A guess, you probably need an index on empcode on one or more of the underlying tables. Really don't know as we could also use the DDL (including index definitions) for the underlying tables and the actual execution plan saved as .sqlplan and zipped for uploading.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Tara (7/20/2009)
I have this simple query for 10 records and it is taking more than an hour, not sure where to look into for exact reason.
select top 10* from vwemp whwere empcode='BHK09287345'
where clause on other fields comes back in seconds but only this field taking long. This filed is not part of the index.
check the showplan attached.

That's a view and if it doesn't have aggregates in it, then you've picked a column where either the EmpCode is part of another view or it's a "key" to the view where the whole view must be resolved in order to determine what the top 10 actually is according to the returns with that filter.

It's going to take you some work to figure out the problem and you do stand the chance of messing up other columns in the process of fixing this one.

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

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

Yes, there is a big view in which it queries 2 large tables and ofcourse this column does nto have any index it has to use the availabe best index to get the result.

At this point i may not be able to change any indexs on the table but how about adding an idex to this column just on the view. I have never done any idexed views so far, could please help me is that is appropriate.

thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Yes, you could potentially make the view an indexed view. You really need to read about those in BOL (Books Online, the SQL Server Help System) before going down that path. There are requirements that have to be met to create an indexed view.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

I have UNION ALL in my view so i think i cant create an index on that view as it has UNION in it. any other suggestions.

thanks

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

As i have union all from 2 tables, i thout of creating to seperate view for each table with indexes and join them in 3rd view but when creating indexed vies i am getting another error now like..

Msg 10139, Level 16, State 1, Line 1
Cannot create index on view 'Emp_rev.dbo.vwemp_a'
because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

I dont have any conversion except this column in the view

case when servcdate < '2004-01-01' then empchit else jobchit end as ejchit

Here servcdate has smalldatetime datatype.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Read this article How to Post Performance Problems[/url].

Follow the instructions in it regarding what needs to be posted so that we can provide you with better answers.

Without that information the only thing I'd suggest is to add an index on the appropriate table(s) for the column in question.

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

Tara (7/21/2009)
As i have union all from 2 tables, i thout of creating to seperate view for each table with indexes and join them in 3rd view but when creating indexed vies i am getting another error now like..
Msg 10139, Level 16, State 1, Line 1
Cannot create index on view 'Emp_rev.dbo.vwemp_a'
because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.
I dont have any conversion except this column in the view
case when servcdate < '2004-01-01' then empchit else jobchit end as ejchit
Here servcdate has smalldatetime datatype.

So, change it...

case when servcdate < CAST('2004-01-01' AS DATETIME) then empchit else jobchit end as ejchit

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

Tara-1044200 SSCoach Points: 15951 More actions · Answer 9

i still get this error

Cannot create index on view . The view contains a convert that is imprecise or non-deterministic.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Tara (7/21/2009)
i still get this error
Cannot create index on view . The view contains a convert that is imprecise or non-deterministic.

So change it again:

case when servcdate < CONVERT(datetime, '20040101', 112) then empchit else jobchit end as ejchit

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 11

As a sidebar, I wonder why they think that's non-deterministic. Same thing goes with many of the date functions.

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