I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.
--Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
)
INSERT INTO @.tempA
SELECT
LNKEY
,AuditorID
FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application
--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
)
INSERT INTO @.tempB
SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID
/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/
DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000)
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA
OPEN c1
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
END
CLOSE c1
DEALLOCATE c1Patti wrote:
Quote: Originally Posted by I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.
>
>
--Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
)
>
INSERT INTO @.tempA
>
SELECT
LNKEY
,AuditorID
>
FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application
>
>
--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
)
>
INSERT INTO @.tempB
>
SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID
>
>
>
/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/
>
DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000)
>
>
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA
>
OPEN c1
>
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
>
WHILE @.@.FETCH_STATUS = 0
BEGIN
>
Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)
>
>
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
>
END
>
CLOSE c1
DEALLOCATE c1 |
Untested:
insert into tblInjectLoans (lnkey, AuditorID)
select lnkey, AuditorID
from tblALPSLoans al
where AuditDate between @.BegDate and @.EndDate
and AuditorID in (
select lanid
from tblEmployees
where actiontype = 'ADDED'
)
and AuditType = @.AuditType
and (select count(lnkey)
from tblInjectLoans il
where il.AuditorID = al.AuditorID
)
< (select round(count(lnkey) * @.Percent/100, 0)
from tblALPSLoans al2
where al2.AuditDate between @.BegDate and @.EndDate
and al2.AuditorID = al.AuditorID
)|||This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated.
Ed Murphy wrote:
Quote: Originally Posted by Patti wrote:
> |
Quote: Originally Posted by I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance. --Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
) INSERT INTO @.tempA SELECT
LNKEY
,AuditorID FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application --Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
) INSERT INTO @.tempB SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID /*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/ DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000) DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA OPEN c1 FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID WHILE @.@.FETCH_STATUS = 0
BEGIN Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID) FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID END CLOSE c1
DEALLOCATE c1 |
>
Untested:
>
insert into tblInjectLoans (lnkey, AuditorID)
select lnkey, AuditorID
from tblALPSLoans al
where AuditDate between @.BegDate and @.EndDate
and AuditorID in (
select lanid
from tblEmployees
where actiontype = 'ADDED'
)
and AuditType = @.AuditType
and (select count(lnkey)
from tblInjectLoans il
where il.AuditorID = al.AuditorID
)
< (select round(count(lnkey) * @.Percent/100, 0)
from tblALPSLoans al2
where al2.AuditDate between @.BegDate and @.EndDate
and al2.AuditorID = al.AuditorID
)|||Patti (pdavis269@.worldsavings.com) writes:
Quote: Originally Posted by This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated. |
So does the original code you posted produce the correct result or
not? This is not clear to me.
A good idea for this type of questions, is that you post:
o CREATE TABLE statements for your tables, preferrably simplified to
the pertinent columns.
o INSERT statement with sample data.
o The desired result given the sample.
This make it easy to copy and paste and develop a tested solution. Also
the test data helps to clarify the verbal resitriction.
Note that the amount of sample data can be fairly small, but it should
be big enough to cover important cases.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance.
/*This is how the tblInjectLoans table
looks like before I start my cursor.*/
LNKEY AuditorID
000001 lpAAAAA
000002 lpBBBBB
000003 lpCCCCC
/*I then need to find 3 percent of completed loans for each auditor and
insert it into
a temp table*/
INSERT INTO @.tempB
SELECT
ROUND(COUNT(LNKEY) * 3/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
GROUP BY AuditorID
/*Results of @.TempB insert
AuditorID LnkeyCount
lpAAAAA 3
lpBBBBB 2
lpCCCCC 1
*/
/*Create cursor to loop through records and add a loan number to
tblinjectloans table if the number of loans in tblinjectloans for each
auditor is less than the LnkeyCount for each auditor from
@.tempB*/
DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1 int
DECLARE @.var2 int
DECLARE @.sql varchar(4000)
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA
OPEN c1
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where
AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
END
CLOSE c1
DEALLOCATE c1
/*Desired results of tblInjectLoans when cursor is done
LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC
*/
As you can see each auditor's count of loans is equal to the number of
LNKEYCount from @.TempB. This is my ultimate desired results. I need
loans added to the tblInjectLoans for each auditor until the total for
that auditor reaches their LNKEYCount from @.tempB. My original code
does produce these results. It just takes a long time to run.
Erland Sommarskog wrote:
Quote: Originally Posted by Patti (pdavis269@.worldsavings.com) writes: |
Quote: Originally Posted by This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated. |
>
So does the original code you posted produce the correct result or
not? This is not clear to me.
>
A good idea for this type of questions, is that you post:
>
o CREATE TABLE statements for your tables, preferrably simplified to
the pertinent columns.
o INSERT statement with sample data.
o The desired result given the sample.
>
This make it easy to copy and paste and develop a tested solution. Also
the test data helps to clarify the verbal resitriction.
>
Note that the amount of sample data can be fairly small, but it should
be big enough to cover important cases.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Patti (pdavis269@.worldsavings.com) writes:
Quote: Originally Posted by My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance. |
You did not say which version of SQL Server you are using. The solution
below works on SQL 2000, but on SQL 2005 it should be possible to
all in one query.
First, add this column to @.TempA:
rowno int IDENTITY
No you can insert all rows at once with:
INSERT dbo.tblInjectLoans (lnkey, AuditorID)
SELECT a.lnkey, a.auditorid
FROM @.TempA a
JOIN @.TempB b ON a.AuthorID = b.AuthorIS
WHERE b.lnkey >
a.rowno + (SELECT COUNT(il.Lnkey)
FROM tblInjectLoans il
WHERE il.AuditorID = a.AuditorID)
Since you did not include a repro script (i.e. the CREATE TABLE and
INSERT statements I was asking for) this untest.
Note that the code as you have written is not deterministic in which
loans that goes to which auditor, but nor is it anything that can be
called random.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need:
tblInjectLoans:
CREATE TABLE [tblInjectLoans] (
[lnkey] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QualityAuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_tblInjectLoans] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
tblALPSLoans:
CREATE TABLE [tblALPSLoans] (
[IDX] [int] IDENTITY (1, 1) NOT NULL ,
[LNKEY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuditorID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditDate] [datetime] NULL ,
[AuditType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblALPSLoans] PRIMARY KEY CLUSTERED
(
[IDX]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I tried your suggestion and it is giving me all loans from temp table A
for each auditor where the count of loans from tblInjectLoans is
greater than the lnkeycount from temp table B. I do not want all loans
from temp table A, I want enough loans inserted until the lnkeycount
for each auditor is reached. Ex.
lnkeycount for Auditor lpAAAA = 3
lnkeycount for Auditor lpBBBB = 2
lnkeycount for Auditor lpCCCC = 1
Results:
LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC
Thanks in advance!
Erland Sommarskog wrote:
Quote: Originally Posted by Patti (pdavis269@.worldsavings.com) writes: |
Quote: Originally Posted by My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance. |
>
You did not say which version of SQL Server you are using. The solution
below works on SQL 2000, but on SQL 2005 it should be possible to
all in one query.
>
First, add this column to @.TempA:
>
rowno int IDENTITY
>
No you can insert all rows at once with:
>
INSERT dbo.tblInjectLoans (lnkey, AuditorID)
SELECT a.lnkey, a.auditorid
FROM @.TempA a
JOIN @.TempB b ON a.AuthorID = b.AuthorIS
WHERE b.lnkey >
a.rowno + (SELECT COUNT(il.Lnkey)
FROM tblInjectLoans il
WHERE il.AuditorID = a.AuditorID)
>
>
Since you did not include a repro script (i.e. the CREATE TABLE and
INSERT statements I was asking for) this untest.
>
Note that the code as you have written is not deterministic in which
loans that goes to which auditor, but nor is it anything that can be
called random.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Patti (pdavis269@.worldsavings.com) writes:
Quote: Originally Posted by I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need: |
A repro script is something that reproduces something. The term is maybe
most commonly used in conjunction with bugs. That is, if you think that
you have found a bug, I would ask you for away to reproduce the problem.
In this case, I suggested a couple of posts back that you should post
CREATE TABLE statements for your tables and INSERT statements with
sample data, as well as the desired result give the sample. Calling
this a repro is maybe inaccurate. It is really a unit test, at least
if the sample data is well chosen.
You posted the table this time, but not the sample data. So I am sorry,
but I will not take any more stab at your problem. I would have to
guess too much. I'm sorry that the solution in my previous post did
not work. Maybe I misunderstood the requirements, maybe I made a
mistake when I composed the solution. Without test data, and without
the expected result from the test data, it is very difficult to write
a usable solution.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Patti (pdavis269@.worldsavings.com) writes:
Quote: Originally Posted by I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need: |
By the way, you still have not said which version of SQL Server you are
using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
