Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: Improve performance on a query with UNION

    I have a view which uses UNION of two tables. First table has a 1.5 Million records and the second one has 40,000 records. When I query the view with a column (that is indexed in both tables) in the where clause, it's taking taking 3 Minutes to give the result. The column is of DateTime data Type. Any ideas as to how to improve the query performance ???

    TIA

    -XLDB

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Look at the query plan to see what the heck it is doing. Run both of the queries separately, and time them. At this point, I don't know enough to offer any reasonable suggestions.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Look at the query plan to see what the heck it is doing. Run both of the queries separately, and time them. At this point, I don't know enough to offer any reasonable suggestions.

    -PatP
    Well yeah..

    How about the DDL of the tables including the indexes and the ddl of the view and the dml of your select statement..

    Got any stage 2 predicates?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2003
    Posts
    61
    Whats stage 2 predicates?

  5. #5
    Join Date
    Dec 2003
    Posts
    61
    Create Table1(Date1 Datetime)

    Create Table2(Date2 Datetime)

    Create View View1 as
    Select Date1 as [dtDate] from Table1
    Union All
    Select Date2 as [dtDate] from Table2

    Select *
    from View1 Where dtDate between '01/01/2004' and '06/10/2004'

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by forXLDB
    Create Table1(Date1 Datetime)
    Where dtDate between '01/01/2004' and '06/10/2004'
    That's a stage 2 predicate....one which can not use an index

    Use Show plan to see what the query is doing...


    WHERE dtDate >= '01/01/2004'
    AND dtDate <= '06/10/2004'

    is not....


    general rule of thumb...any function applied to a column will cause a scan
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    61
    If I look at Display Estimated Execution Plan....
    The following queries use the index
    Select Date1 as [dtDate] from Table1 where Date1 Between '01/01/2004' and '06/10/2004'

    Select Date2 as [dtDate] from Table2 where Date2 Between '01/01/2004' and '06/10/2004'

    But, the following view doesn't use any index...
    Select *
    from View1 Where dtDate between '01/01/2004' and '06/10/2004'

    Also...I still dont' get stage 2 predicate stuff....it's using when run on individual tables

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My bad about between...

    Do you have an index on the date column?

    It should be doing an index scan...

    And do you need all the columns?

    I've seen some bad things with select *

    http://weblogs.sqlteam.com/brettk/ar...4/22/1272.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    61
    OK.
    Yes. I do have indexed on the date column in two tables.

    I can use
    Select dtDate instead of Select *.

    Still waiting to have the best way improve performance of the view

  10. #10
    Join Date
    Dec 2003
    Posts
    61
    Also, as I said earlier, it is not using any index when I query the view. (Technically, not sure if it can use an index on a view)

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    cut and paste this code and tell me it's not using an index...I get index seek on both

    Code:
    Create table Table1(Date1 Datetime primary key)
    Create table Table2(Date2 Datetime primary key)
    GO
    
    Create View View1 as
    Select Date1 as [dtDate] from Table1
    Union All
    Select Date2 as [dtDate] from Table2
    GO
    
    Select *
    from View1 Where dtDate between '01/01/2004' and '06/10/2004'
    GO
    
    drop view view1
    drop table table1
    drop table table2
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Dec 2003
    Posts
    61
    oh...ya...it works....
    not sure why my stuff doesnt work....
    will try to fetch more details

  13. #13
    Join Date
    Dec 2003
    Posts
    61
    I overlooked the IsNull condition...
    here's the latest...

    Create table Table1(Date1 Datetime primary key, Date2 Datetime)
    Create table Table2(Date2 Datetime primary key)
    GO

    Create View View1 as
    Select IsNull(Date1,Date2) as [dtDate] from Table1
    Union All
    Select Date2 as [dtDate] from Table2
    GO

    Select *
    from View1 Where dtDate between '01/01/2004' and '06/10/2004'
    GO

    --the above view is using the Index Scan rather than seek....Do I need to index the
    --Date2 too ??? If so, how should I create the index ???

    drop view view1
    drop table table1
    drop table table2
    GO

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Aint no thing....

    Code:
    Create table Table1(Date1 Datetime primary key, Date2 Datetime)
    Create table Table2(Date2 Datetime primary key)
    GO
    
    CREATE INDEX Table1_LetsHopeForIntersection ON Table1(Date2)
    GO
    
    Create View View1 as
    Select Date1 as [dtDate] from Table1 WHERE Date1 IS NOT NULL
    Union All
    Select Date2 as [dtDate] from Table1  WHERE Date1 IS NULL
    Union All
    Select Date2 as [dtDate] from Table2
    GO
    
    Select *
    from View1 Where dtDate between '01/01/2004' and '06/10/2004'
    GO
    
    --the above view is using the Index Scan rather than seek....Do I need to index the 
    --Date2 too ??? If so, how should I create the index ???
    
    drop view view1
    drop table table1
    drop table table2
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Dec 2003
    Posts
    61
    Assume Date1 is not a primary key... (Date1 is indexed..but not a primary clustered one)

    Select IsNull(Date1,Date2) as [dtDate] from Table1
    is required (thats how my view is setup currently)
    Also, I cannot ignore records where Date is Null


    PS: I have lots of other columns in both tables, I just gave only the columns required for the discussion

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •