Querry slow

Posted on 2007-10-20
Last Modified: 2010-03-19
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:
Question by:so3
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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...
    LVL 6

    Author Comment

    - 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?
    LVL 142

    Accepted Solution

    UNION ... a UNION or UNION ALL... should be UNION ALL

    in regards to the statistics, run this query:

    LVL 11

    Expert Comment

    How long does query 2 take without the select at the end? It should be as good as instant.
    LVL 6

    Author Comment

    - 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?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 6

    Author Comment

    thanks for the help

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Pivot Query Problem 9 25
    best way to upgrade 3 29
    Software suggestion 12 18
    How to query LOCK_ESCALATION 4 22
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now