Post reply

How to optimize this sql query with 290 columns

  • November 10, 2016 at 11:00 am

    #315537

    Hi,

    I have a very large table. The current query

    has about 290 columns and the query selects them all.

    select Column1 ... Column290

    from dob.table1

    where (ISNULL(Column50, 1) = 1)

    It returns about 1562699 in about 8:15

    I added a Non-Unique and Non-Clustered index and it only contains Column50

    How can I further optimize and improve the speed?

    Thanks

  • November 10, 2016 at 11:25 am

    #1911737

    Quick thought, return less columns. Why or when will you ever need all those columns and all those rows?

    Also, do not use ISNULL, use WHERE (Column50 = 1 OR Column50 IS NULL) to allow index seeks.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 10, 2016 at 11:26 am

    #1911739

    You're performing a function against the column. That's going to lead to scans. You need to not do that. Using IS NOT NULL is more likely to perform well. However, it looks like you're returning millions of rows and hundreds of columns. Tuning this type of query pattern either consists of reducing the rows & columns to move only the data you actually need, or tuning the hardware since you're not going to be able to take advantage of indexes in a meaningful way when moving a substantial portion of the entire table.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 10, 2016 at 11:52 am

    #1911746

    1) Indexing a column doesn't mean it will be used for an index seek operation - even if you refactor your WHERE clause as specified. If the optimizer estimates your query will return roughly 1% or more of the total rows it will switch to a table scan as the lower cost operation.

    2) You are returning 1.6M rows of fat data and expecting it to be quick?? Why? If you want to test how fast SQL Server is processing the data declare variables for all columns (of the proper data type!!) and SELECT the columns into the variables. Best you this happens WAY faster than 8+ minutes!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

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