none
Improving performance

    Question

  • Hi,

    I've got a table that contains 6 columns: SourceHub, Date, Network, QOS, Allocated, BPS

    The table contains about 1 million lines.

    Columns that doesn't have much diversity are : SourceHub, Network and QOS - On those I have already created Clustered Index.

    What can I do in order to improve queries run time? Create more indexes? What kind? Will deleting columns help?
    Monday, July 01, 2013 3:43 PM

Answers

  • Hi,

    Try the below optimization points.

    1. Dont use select *, instead of that mention particualr column which you required

    2. Use IN clause instead of OR.

    3. While using IN clause, try to implement Index Seek - which will give better performance.

    Pls mark as answered, if my post is helpful to you.

    -Sugumar P

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 3:50 PM
  • And also,

    If you have only read transaction then you can go for index, since index will reduce write performance transactions.

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 3:52 PM
  • Hi,

    The thing is that the queries are not running from the DB. I'm using reporting services. But my basic queries on the reporting services are without * and "OR".

    What kind of index should I use? Can you help me with the syntax?

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 4:42 PM
  • We can't say much at all about this without knowing what queries you are running.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:04 PM
  • Most of the queries running from reporting services will be:

    Select SourceHub, Date, Network, QOS, Allocated, BPS

    from test

    where QOS in (Value)

    and Date between Date1 and Date2

    order by Date

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:15 PM
  • Can you try creating a NCI on QOS and Date columns and check.

    Date is a reserved keyword, using it as variable/entity is bad practice.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:21 PM

All replies

  • Hi,

    Try the below optimization points.

    1. Dont use select *, instead of that mention particualr column which you required

    2. Use IN clause instead of OR.

    3. While using IN clause, try to implement Index Seek - which will give better performance.

    Pls mark as answered, if my post is helpful to you.

    -Sugumar P

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 3:50 PM
  • And also,

    If you have only read transaction then you can go for index, since index will reduce write performance transactions.

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 3:52 PM
  • Hi,

    The thing is that the queries are not running from the DB. I'm using reporting services. But my basic queries on the reporting services are without * and "OR".

    What kind of index should I use? Can you help me with the syntax?

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 4:42 PM
  • We can't say much at all about this without knowing what queries you are running.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:04 PM
  • Most of the queries running from reporting services will be:

    Select SourceHub, Date, Network, QOS, Allocated, BPS

    from test

    where QOS in (Value)

    and Date between Date1 and Date2

    order by Date

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:15 PM
  • Can you try creating a NCI on QOS and Date columns and check.

    Date is a reserved keyword, using it as variable/entity is bad practice.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by ThomasYaris30 Monday, July 01, 2013 5:55 PM
    Monday, July 01, 2013 5:21 PM
  • How many rows typically matches the IN clause?

    How may rows typically matches the BETWEEN clause?

    How many rows typically matches the combination of the two?

    How many rows are there in the table?

    Is creating a clustered index an option, or do you have to keep the one you are currently having?

    Is it OK to have fairly fat indexes? I.e., can you accept that the total size grow, potentially a lot, and also the cost for modifications  increases?

    Here's a few of potential indexes

    CREATE CLUSTERD INDEX x ON tbl(Date, QOS)

    CREATE INDEX x ON tbl(Date, Qos)

    CREATE INDEX x ON tbl(Date, QOS) INCLUDE(SourceHub, Network, Allocated, BPS)


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 01, 2013 5:27 PM
  • Is there any unique or primary key constraint on this table?

    Based on your query I would start by clustering on the [date] column. This will help with the range filter and the ORDER BY clause.


    AMB

    Some guidelines for posting questions...

    Monday, July 01, 2013 5:31 PM