Get Data from Table in Less time

Question

Hi I am using SQL Server 2008. In my database there is one table called MemberBusiness. I want to get data from that table .

This is my stored procedure

SELECT    BrokerId ,
          RankId ,
          MemberId ,
          InstallmentId ,
          PlanId ,
          IntroducerId ,
          Date ,
          SelfAmount ,
          UnitAmount ,
          SpotAmount ,
          ORBPercentageSelf ,
          ORBPercentageUnit ,
          ORBAmountSelf ,
          ORBAmountUnit ,
          IsSelfBusiness ,
          Mode ,
          InstallmentNo ,
          PlanType ,
          PlanName ,
          CompanyId ,
          CscId ,
          Year ,
          CreateDate ,
          ModifideDate
FROM      dbo.MemberBusiness AS mb
WHERE     ( @CscId = 0
            OR mb.CscId = @CscId
          )
          AND ( @CompanyId = 0
                OR CompanyId = @CompanyId
              )
          AND BrokerId IN ( SELECT    bt.BrokerId
                            FROM      #brokerTable AS bt )
          AND mb.Date >= @StartDate
          AND mb.Date <= @EndDate
          AND mb.RankId >= @FromRankId
          AND mb.RankId <= @ToRankId

In MemberBusiness table there is approx 16560352 records. Above SP gives me 1300 records and takes approx 30 seconds to execute which is not acceptable for me. I have used Indexing but still its take long time to execute. How can do this faster?. Thanks.

`@CompanyId = 0 OR CompanyId = @CompanyId` etc will prevent proper index usage unless you use `OPTION (RECOMPILE)`. Try adding that and if you still get the problem post the execution plan and table definition including indexes. — Martin Smith, Oct 04 '13 at 11:37
Changing that 'in' to a join would be one, thing. But first thing to do is prefix the query with explain and it will tell you what it's doing. You could add that output to the question and may be we can come up with an idea or two. — Tony Hopkinson, Oct 04 '13 at 11:39
@TonyHopkinson - `IN` is a semi join. There is no reason to thing writing it as a join will improve anything. It can change the semantics as well in that it may bring back duplicates that then need to be removed with `DISTINCT`. — Martin Smith, Oct 04 '13 at 11:40
possible duplicate of [Improve SQL Server query performance on large tables](http://stackoverflow.com/questions/10025569/improve-sql-server-query-performance-on-large-tables) — coding Bott, Oct 04 '13 at 12:07
Point taken, twas only a guess. In is always worth a glance optimisation wise — Tony Hopkinson, Oct 04 '13 at 20:50
This question appears to be off-topic because it misses information such as table definitions and execution plans required for a non speculative answer. — Martin Smith, Mar 01 '14 at 01:56

score 0 · Answer 1 · answered Oct 04 '13 at 11:37

A couple suggestions:

Get rid of that inner select, instead make it an inner join like this:

SELECT BrokerId, ... FROM dbo.MemberBusiness AS mb JOIN #brokerTable as bt ON bt.BrokerID=mb.BrokerID WHERE mb.CscID=@CscID...
Those OR statements could be slow... can you get rid of them or do a union with a different query?
Make sure you have a big single index that covers all of the columns you're comparing like this:

CREATE INDEX mySearchIndex ON MemberBusiness ( CscId ASC, CompanyID ASC, BrokerId ASC, Date ASC, RankId ASC)

I have already created INDEX as you say..but join with #brokerTable takes more time then my query. — Prashant16, Oct 04 '13 at 11:52

score 0 · Answer 2 · answered Oct 04 '13 at 12:04

You have many ways to improve your query :

First of all, link your #brokerTable with an inner join clause,
You need to avoid the OR clause. As I am not a big fan of dynamic SQL, you can use IF statements like showxn below,
Finally you need to design your indexes, both CLUSTERED and NONCLUSTEREd, and test the impact with query plans.

SQL :

IF @CscId = 0 AND @CompanyId = 0
BEGIN

    SELECT  mb.BrokerId,
            mb.RankId,
            mb.MemberId,
            mb.InstallmentId ,
            mb.PlanId ,
            mb.IntroducerId ,
            mb.Date ,
            mb.SelfAmount ,
            mb.UnitAmount ,
            mb.SpotAmount ,
            mb.ORBPercentageSelf ,
            mb.ORBPercentageUnit ,
            mb.ORBAmountSelf ,
            mb.ORBAmountUnit ,
            mb.IsSelfBusiness ,
            mb.Mode ,
            mb.InstallmentNo ,
            mb.PlanType ,
            mb.PlanName ,
            mb.CompanyId ,
            mb.CscId ,
            mb.Year ,
            mb.CreateDate ,
            mb.ModifideDate
    FROM    dbo.MemberBusiness AS mb
                INNER JOIN #brokerTable AS bt ON mb.BrokerId = bt.BrokerId
    WHERE mb.Date >= @StartDate
            AND mb.Date <= @EndDate
            AND mb.RankId >= @FromRankId
            AND mb.RankId <= @ToRankId

END
ELSE IF @CscId = 0 AND @CompanyId <> 0
BEGIN

    SELECT  mb.BrokerId,
            mb.RankId,
            mb.MemberId,
            mb.InstallmentId ,
            mb.PlanId ,
            mb.IntroducerId ,
            mb.Date ,
            mb.SelfAmount ,
            mb.UnitAmount ,
            mb.SpotAmount ,
            mb.ORBPercentageSelf ,
            mb.ORBPercentageUnit ,
            mb.ORBAmountSelf ,
            mb.ORBAmountUnit ,
            mb.IsSelfBusiness ,
            mb.Mode ,
            mb.InstallmentNo ,
            mb.PlanType ,
            mb.PlanName ,
            mb.CompanyId ,
            mb.CscId ,
            mb.Year ,
            mb.CreateDate ,
            mb.ModifideDate
    FROM    dbo.MemberBusiness AS mb
                INNER JOIN #brokerTable AS bt ON mb.BrokerId = bt.BrokerId
    WHERE CompanyId = @CompanyId
            AND mb.Date >= @StartDate
            AND mb.Date <= @EndDate
            AND mb.RankId >= @FromRankId
            AND mb.RankId <= @ToRankId

END
ELSE IF @CscId <> 0 AND @CompanyId = 0
BEGIN

    ...

END
ELSE
BEGIN

    ...

END

Then the big part, the indexes. You need at least two indexes : - One clustered

CREATE CLUSTERED INDEX [IX_MemberBusiness]
ON [dbo].[MemberBusiness]
(
    RankId,
    Date,
    CompanyId,
    CscId,
    [The real ID of your table]
)

One non clustered

CREATE NONCLUSTERED INDEX [IX_MemberBusiness_nc] ON [dbo].[MemberBusiness] ( RankId, Date, CompanyId, CscId, [The real ID of your table] ) INCLUDE ( [All fields in the select except those in the index] )

The clustered index must include fields on which you make range queries (ie. Date and RankId). The nonclustered must include fields to "peak" data (ie. BrokerId, CompanyId, CscId). Like the ids.

You have some work to do on the index design, by testing which field is relevant in the clustered and non clustered, by using the query plan in SMSS.

This technic helps me to improve drastically the performance of my project by a factor of 3.

Let me know if it works for you.

score -1 · Answer 3 · answered Oct 04 '13 at 11:40

-1

May be these can help in a right direction

Make a view for your select query logic
For your sp make the query dynamic with the @searchstring a dynamic paramter which holds your search string
Try to make your indexing like @JerSchneid suggest.

answered Oct 04 '13 at 11:40

nrsharma

2,532
3
20
36

@downvoter: these are only suggestions to think in right direction, if it helps; why downvote here? – nrsharma Oct 04 '13 at 11:48

score -2 · Answer 4 · answered Oct 04 '13 at 11:42

This is all about tuning and scalability.

Frist: Check you statement with the Query Analyser and create indexes and statistics. don't forget to drop indexes that are not used. try to rearrange the where clause, for optimal access of the data. You can also use the QA to create indexes and statistics for you.

think about partitioning the table.

If it still is to slow, think in hardware. Find the bottleneck and use more RAM, bigger cpu or cpu's, get some SSD's, a faster network. try to share the load in a cluster.

thx, for the down votes - but provide an information what is wrong/you don't like! Pointing to find the bottlenecks with tools like QA, doesn't sound wrong. — coding Bott, Oct 04 '13 at 12:05

Get Data from Table in Less time

4 Answers4