• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Querry slow

I have a view that returns 1300 records and when executed with variables takes 3 seconds. if i run it with static parameter takes 0 seconds. Why is this happening?

The querries are:
1. SELECT * FROM FACC_V_PRELUARE_CONT_BUFFER WHERE DAT_DOCUMENT between '08/01/2007' and '08/31/2007'
--this take 0 seconds

declare @currentdate datetime
declare @fistday datetime
declare @lastday datetime
set @currentdate=cast('8/26/2007' as datetime)

SET @fistday = DATEADD(DD, 1 - DAY(@currentdate), @currentdate)
SET @lastday = DATEADD(DD, -1, DATEADD(M, 1, @fistday))

            WHERE DAT_DOCUMENT between @fistday  and @lastday
--takes 3 seconds

Both querries returns the exact number of records.
How can i modify the querry to get all the records in a month and takes 0 seconds?

this also takes 3 seconds to complete:
  • 3
  • 3
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type of the column DAT_DOCUMENT?
are the statistics up-to-date on that table?
what indexes do you have on that table?
is FACC_V_PRELUARE_CONT_BUFFER actually a view?

can you provide some information on how the execution plan differs for the 2 first queries?

note that the last suggestion is the worst you could have :) while the second looks absolutely fine...
so3Author Commented:
- column DAT_DOCUMENT is datetime of corse
- FACC_V_PRELUARE_CONT_BUFFER yes it is a view
- i have index on the main table that has column dat_document but the view FACC_V_PRELUARE_CONT_BUFFER use the column DAT_DOCUMENT from another view(which is actualy a union from several tables)

- are the statistics up-to-date on that table? -- sorry i don't know what do yo refer

Do you have any idea why the time on the 2 querries are different since the query results are the same?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UNION ... a UNION or UNION ALL... should be UNION ALL

in regards to the statistics, run this query:

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

How long does query 2 take without the select at the end? It should be as good as instant.
so3Author Commented:
- When using DBCC SHOW_STATISTICS i get an error not enough parameter
- I've put union all in that view instead of union and now the querry 2 takes 0 seconds, but if i order it by DAT_document it increases at 3 sec.
Can you explain the diference between union and union all because it seems that have impact on performance(at least on querry 2)?

if i remove the where condition it takes 0 seconds. It seems that when using in the querry column dat_document takes it long.

If the main table has an index on column dat_document, why the other querries(views) don't use that index? i'm doing wrong someting or what?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
union vs union all:
union all does, implicitely, perform a DISTINCT on the resultset, which most beginners don't know. this will of course have results on the performance, but also potentially on the returned records. most of the time, though, union all is perfectly fine, and union could even "spoil" the wanted results...

now, when you add an order by, it will, of course, have to sort the results.
you might consider using a temp table (or better a table variable) to put the result unsorted in there, and then query from that table using the order by.
that will avoid that the execution plan get's impacted on the original view by using the order by.

that will also help you to avoid the SELECT * syntax, as that is not recommended, as simply, you don't need all columns.
so3Author Commented:
thanks for the help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now