if a query takes 7 minutes to complete with cursor, how to manage this

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi all!

if a query takes 7 minutes to complete with cursor
so what is the replacement of Cursors.

Query is that we want to get the count of relevant merchants while showing the list of Merchants

Table Having MerchantID and its CategoryID to whom this mercahnt belongs to.

Table Date is:

MercahtnID CategoryID
45 | 2
45 | 3
45 | 4
46 | 3
46 | 4
47 | 2

So on the basis of category i have to get the relevant merchants
e.g there are 2 relevant merchants of 45
there is 1 relevant merchants of 46 and 47

What is the query which gets count of relevant merchants of each merchant.

Thanks in Advance, if any body have any idea other than using cursor, please tell me.

Regards,
Jojo

Posted 27-Apr-10 6:49am

jojolili

Add a Solution

4 solutions

Solution 1

Cursors exist for a reason., Sometimes you have to use them. However, I'd imagine a group by clause is what you need in order to do what you want without a cursor.

It's also possible your existing code could use cursors and be faster.

Posted 27-Apr-10 9:35am

Christian Graus

Solution 2

Hi Christian Graus
Thanks to answer me, but i already used Group by class to get that

Following are the solutions.
Solution 1: Using Cursors
Solution 2: Using Group BY class

First solution give me correct answer.
Second solution give incorrect answer as you already suggest me to use group by class

Using Cursors:

SQL

Create table Merchants (MerchantID int)
Create Table MerchantCategory(CategoryID int, MerchantID int)

insert into Merchants values (45)
insert into Merchants values (46)
insert into Merchants values (47)

insert into MerchantCategory values (2,45)
insert into MerchantCategory values (3,45)
insert into MerchantCategory values (4,45)

insert into MerchantCategory values (3,46)
insert into MerchantCategory values (4,46)

insert into MerchantCategory values (2,47)

Select M.MerchantID, 0 as Total into #Temp1
from Merchants M WITH (NOLOCK)


DECLARE @MerchantID INT
DECLARE @getMerchantID CURSOR
SET @getMerchantID = CURSOR FOR
SELECT MerchantID
FROM #Temp1
OPEN @getMerchantID
FETCH NEXT
FROM @getMerchantID INTO @MerchantID
WHILE @@FETCH_STATUS = 0
BEGIN

Select  Distinct

                MC.MerchantID
                Into #Temp2
        FROM
                MerchantCategory MC WITH (NOLOCK)
        WHERE MC.CategoryID in (SELECT MC1.CategoryID  FROM MerchantCategory MC1 WHERE MC1.MerchantID = @MerchantID)
            AND MC.MerchantID <> @MerchantID



Declare @CountTotal int

set @CountTotal = (select COUNT(*) from #Temp2)

update #Temp1  SET Total = @CountTotal
where #Temp1.MerchantID= @MerchantID

Drop table #Temp2

FETCH NEXT
FROM @getMerchantID INTO @MerchantID
END
CLOSE @getMerchantID
DEALLOCATE @getMerchantID


select * from #Temp1 where Total > 0

drop table #Temp1

drop table Merchants
drop table MerchantCategory

Using Group BY class:

SQL

Create table Merchants (MerchantID int)
Create Table MerchantCategory(CategoryID int, MerchantID int)

insert into Merchants values (45)
insert into Merchants values (46)
insert into Merchants values (47)

insert into MerchantCategory values (2,45)
insert into MerchantCategory values (3,45)
insert into MerchantCategory values (4,45)

insert into MerchantCategory values (3,46)
insert into MerchantCategory values (4,46)

insert into MerchantCategory values (2,47)

Select M.MerchantID, 0 as Total into #Temp1
from Merchants M WITH (NOLOCK)


SELECT Count(MC.MerchantID) AS CountMerchantID,MC.CategoryID
INTO #Temp5
FROM MerchantCategory MC
WHERE MC.CategoryID in (SELECT MC1.CategoryID FROM MerchantCategory MC1)
GROUP BY MC.CategoryID
ORDER BY MC.CategoryID

SELECT  Count(MC.MerchantID) AS CountSpecificMerchantID,MC.MerchantID AS SpecificMerchantID
INTO #Temp51
FROM MerchantCategory MC
WHERE MC.CategoryID in (SELECT MC1.CategoryID FROM MerchantCategory MC1)
GROUP BY  MC.MerchantID
ORDER BY MC.MerchantID

SELECT MC.CategoryID,t5.CountMerchantID,MC.MerchantID
INTO #Temp6
FROM MerchantCategory MC
    INNER JOIN #Temp5 t5
    ON t5.CategoryID = MC.CategoryID
ORDER BY MC.MerchantID

SELECT  SUM(t6.CountMerchantID) AS RelevantInclusiveMerchantsCount, t6.MerchantID AS t7MerchantID
INTO #Temp7
FROM #Temp6 t6
GROUP BY t6.MerchantID
ORDER BY t6.MerchantID

UPDATE #Temp1 SET Total = (SELECT (isNull(t7.RelevantInclusiveMerchantsCount,0)-(isnull(t51.CountSpecificMerchantID,0))) as RelevantMerchantsCount  FROM #Temp7 t7 INNER JOIN #Temp51 t51 ON t7.t7MerchantID = t51.SpecificMerchantID  WHERE t7.t7MerchantID = #Temp1.MerchantID AND t51.SpecificMerchantID = #Temp1.MerchantID)

SELECT t1.*,t7.RelevantInclusiveMerchantsCount,t51.CountSpecificMerchantID
FROM #Temp1 t1
    LEFT OUTER JOIN #Temp7 t7
    ON t1.MerchantID = t7.t7MerchantID
    LEFT OUTER JOIN #Temp51 t51
    ON t1.MerchantID = t51.SpecificMerchantID



drop table #Temp1
drop table #Temp5
drop table #Temp51
drop table #Temp6
drop table #Temp7

drop table Merchants
drop table MerchantCategory

Posted 27-Apr-10 23:13pm

jojolili

Solution 4

Hi WoutL

Thanks for giving me such a nice query.

Regards,
Jojo Lili

Posted 28-Apr-10 2:57am

jojolili

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

WoutL · Accepted Answer · 2010-04-28T02:02:00

If I understand your question this query should do the trick

SQL

select m.MerchantID
, Total=(
    select Count(distinct MerchantID) from MerchantCategory mc
    WHERE MC.CategoryID in
    (SELECT MC1.CategoryID  FROM MerchantCategory MC1 WHERE MC1.MerchantID = m.Merchantid)
    And mc.MerchantID <> m.MerchantID
 )
from Merchants m

This way you do not need cursors or temptables. But if your tables have a lot of records, you should add the appropriate indexes.

And like Christian stated: you can also use the group by statement:

SQL

select m.MerchantID, Total=Count(Distinct mc1.MerchantID)
from Merchants m
inner join MerchantCategory mc on mc.MerchantID = m.MerchantID
inner join MerchantCategory mc1 on mc1.MerchantID <> m.MerchantID and mc1.CategoryID = mc.CategoryID
Group by m.MerchantID

if a query takes 7 minutes to complete with cursor, how to manage this

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us