L
LuvinLunch
Hi,
I'm migrating a system from vb6 to vb.net. I developed a stored
procedure (SP) in SQL Server 2000 that has come across to SQL Server
2005 and is being used by the vb6 application fine. When I access the
SP from my new vb.net code though it's really really slow. I'm
looking for advice on how I should rewrite the SP so that it'll work
better with vb.net.
The SP has an input and an output parameter. I suspect the problem is
with the way I did the input parameter. The input parameter is a
string containing a SQL where statement. That statement is used to
query a number of tables. The results of the multiple queries are put
into multiple temporary tables and ultimately into one table. The
reason I passed in a string as the input parameter is that I don't
know what criteria will be used to search in a given query and I
thought it was a good way of getting back an undetermined number of
rows (1-10,000), join them all from temporary tables and return them
all as one recordset. I've included a cleaned up version of my SP
below.
Do you think the query is slow becuase I'm passing in a string?
Should I pass in multiple optional parameters instead? Is there a
smarter way to get my joins working? I'm not sure what part of the SP
is slow.
Thanks for any help you can give.
LL
create procedure [dbo].[sp_MAItems]
(
@WhereSQL nvarchar(3000) = '',
@NumberOfRows int OUTPUT
)
as
begin
/* Constant Declaration */
set nocount on
declare @SUCCESS int,
@FAILURE int,
@SQLCode nvarchar(4000) ,
@GroupBySQL nvarchar(1000) ,
@OfficeSQL nvarchar(200),
@WarehouseSQL nvarchar(200),
@RentSQL nvarchar(200),
@ra_draftflagSQL nvarchar (30),
@ValDraftFlagSQL nvarchar (30), -- D_Cav_Valuations table draft flag
@CAVDraftFlagSQL nvarchar (30) -- D_Cav_Template table draft flag
-- always take the record from D_Rating with ra_draftflag = 1
select @ra_draftflagSQL = ' and (ra_DraftFlag = 1 or ra_DraftFlag = 2)
'
select @WhereSQL = @WhereSQL + @ra_draftflagSQL
--empty summary info table
delete SummaryInfoIndustrial
select @SQLCode = 'select distinct rqserno, rqpropno, estate, street,
occupier, Rent,
TotalM2, termYears, termMonths, datefixed, eaves,
ra_BMID
from vi_MAItems '
select @GroupBySQL = ' group by rqserno, rqpropno, estate, street,
occupier, Rent,
TotalM2, termYears, termMonths, datefixed, eaves,
ra_BMID
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @GroupBySQL
insert into SummaryInfoIndustrial
(
RequestNo, PropNo, Estate, Street, Occupier, Rent, TotalM2,
TermYears, TermMonths, DateFixed, Eaves,
Benchmark
)
exec(@SQLCode)
----------------------------------Temp table for calculating office
sums------------------------------
select @SQLCode = 'select rqpropno, sum(distinct val_m2) as OfficeM2
from vi_MAItems '
select @GroupBySQL = 'group by rqpropno, val_lvl
order by rqpropno'
select @OfficeSQL = ' and (val_newuse = 26 or val_newuse = 95) '
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @OfficeSQL + @GroupBySQL
create table #TempOfficeSums (rqpropno int, sumOfficeValM2 int)
insert into #TempOfficeSums
(
rqpropno, sumOfficeValM2
)
exec(@SQLCode)
--get sums of sums from tempofficesums
select @SQLCode = 'select distinct rqpropno, sum(sumOfficeValM2)
from #TempOfficeSums '
select @GroupBySQL = 'group by rqpropno '
select @SQLCode = @SQLCode + @GroupBySQL
create table #OfficeAvgs
(
rqpropno int, OfficeM2 int
)
insert into #OfficeAvgs
(
rqpropno, OfficeM2
)
exec(@SQLCode)
drop table #TempOfficeSums
--------------------------------- count of Office values per
property-----------------------------------------------
select @SQLCode = 'select rqpropno, count(val_m2) as OfficeCount
from vi_MAItems '
select @GroupBySQL = 'group by rqpropno
order by rqpropno'
select @OfficeSQL = ' and (val_newuse = 26 or val_newuse = 95) '
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @OfficeSQL + @GroupBySQL
create table #OfficeValsCount
(
rqpropno int, OfficeCount int
)
insert into #OfficeValsCount
(
rqpropno, OfficeCount
)
exec(@SQLCode)
--warehouse averages
select @SQLCode = 'select distinct rqpropno, sum(distinct val_m2)
from vi_MAItems '
--select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39
or val_newuse = 14 or val_newuse = 35 or val_newuse = 10) '
select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39 or
val_newuse = 14 or val_newuse = 35 or val_newuse = 10) and val_lvl
<> ''' + 'MEZZ'' '
select @GroupBySQL = 'group by rqpropno, estate, street, occupier,
Rent,
datefixed
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @WarehouseSQL + @GroupBySQL
create table #WarehouseAvgs
(
rqpropno int, WarehouseM2 int
)
insert into #WarehouseAvgs
(
rqpropno, WarehouseM2
)
exec(@SQLCode)
--------------------------------- count of Warehouse values per
property-----------------------------------------------
select @SQLCode = 'select count(val_m2) as WarehouseCount, rqpropno
from vi_MAItems '
--select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39
or val_newuse = 14 or val_newuse = 35 or val_newuse = 10) '
select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39 or
val_newuse = 14 or val_newuse = 35 or val_newuse = 10) and val_lvl
<> ''' + 'MEZZ'' '
select @GroupBySQL = 'group by rqpropno
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @WarehouseSQL + @GroupBySQL
create table #WarehouseValsCount
(
rqpropno int, WarehouseCount int
)
insert into #WarehouseValsCount
(
rqpropno, WarehouseCount
)
exec(@SQLCode)
select @SQLCode = 'select distinct rqpropno, val_topRentM2 from
vi_MAItems '
select @GroupBySQL = 'group by rqpropno, estate, street, occupier,
val_topRentM2,
datefixed
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @GroupBySQL
create table #val_topRentM2
(
rqpropno int, val_topRentM2 money
)
insert into #val_topRentM2
(
rqpropno, val_topRentM2
)
exec(@SQLCode)
--objectIDs
select FileRef as rqpropno, objectID
into #ObjectIDs
from FileDB.dbo.files F
where FileRef in (select PropNo from SummaryInfoIndustrial)
and F.DocType = 4
and F.img_SetMain = 1
--Other column
select PropNo as rqpropno, BMID
into #BMIDs
from BMInformers
where PropNo in (select PropNo from SummaryInfoIndustrial)
--Insert values into summary info
update SummaryInfoIndustrial
set SummaryInfoIndustrial.OfficeM2 = #OfficeAvgs.OfficeM2,
SummaryInfoIndustrial.OfficeCount = #OfficeValsCount.OfficeCount,
SummaryInfoIndustrial.WarehouseM2 = #WarehouseAvgs.WarehouseM2,
SummaryInfoIndustrial.WarehouseCount =
#WarehouseValsCount.WarehouseCount,
SummaryInfoIndustrial.ObjectID = #ObjectIDs.ObjectID,
SummaryInfoIndustrial.Other = #BMIDs.BMID,
SummaryInfoIndustrial.val_topRentM2 = #val_topRentM2.val_topRentM2
from SummaryInfoIndustrial
left outer join #OfficeAvgs on #OfficeAvgs.rqpropno = PropNo
left outer join #OfficeValsCount on #OfficeValsCount.rqpropno = PropNo
left outer join #WarehouseAvgs on #WarehouseAvgs.rqpropno = PropNo
left outer join #WarehouseValsCount on #WarehouseValsCount.rqpropno =
PropNo
left outer join #ObjectIDs on #ObjectIDs.rqpropno = PropNo
left outer join #BMIDs on #BMIDs.rqpropno = PropNo
left outer join #val_topRentM2 on #val_topRentM2.rqpropno = PropNo
--drop temp tables
drop table #OfficeAvgs
drop table #OfficeValsCount
drop table #WarehouseAvgs
drop table #ObjectIDs
drop table #WarehouseValsCount
drop table #BMIDs
drop table #val_topRentM2
--drop table #CalculatedTotalM2
--drop table #RentAvgs
select * from SummaryInfoIndustrial
select @NumberOfRows = @@RowCount
select @NumberOfRows output
--drop table #SummaryInfo
return @SUCCESS
end --End of procedure
I'm migrating a system from vb6 to vb.net. I developed a stored
procedure (SP) in SQL Server 2000 that has come across to SQL Server
2005 and is being used by the vb6 application fine. When I access the
SP from my new vb.net code though it's really really slow. I'm
looking for advice on how I should rewrite the SP so that it'll work
better with vb.net.
The SP has an input and an output parameter. I suspect the problem is
with the way I did the input parameter. The input parameter is a
string containing a SQL where statement. That statement is used to
query a number of tables. The results of the multiple queries are put
into multiple temporary tables and ultimately into one table. The
reason I passed in a string as the input parameter is that I don't
know what criteria will be used to search in a given query and I
thought it was a good way of getting back an undetermined number of
rows (1-10,000), join them all from temporary tables and return them
all as one recordset. I've included a cleaned up version of my SP
below.
Do you think the query is slow becuase I'm passing in a string?
Should I pass in multiple optional parameters instead? Is there a
smarter way to get my joins working? I'm not sure what part of the SP
is slow.
Thanks for any help you can give.
LL
create procedure [dbo].[sp_MAItems]
(
@WhereSQL nvarchar(3000) = '',
@NumberOfRows int OUTPUT
)
as
begin
/* Constant Declaration */
set nocount on
declare @SUCCESS int,
@FAILURE int,
@SQLCode nvarchar(4000) ,
@GroupBySQL nvarchar(1000) ,
@OfficeSQL nvarchar(200),
@WarehouseSQL nvarchar(200),
@RentSQL nvarchar(200),
@ra_draftflagSQL nvarchar (30),
@ValDraftFlagSQL nvarchar (30), -- D_Cav_Valuations table draft flag
@CAVDraftFlagSQL nvarchar (30) -- D_Cav_Template table draft flag
-- always take the record from D_Rating with ra_draftflag = 1
select @ra_draftflagSQL = ' and (ra_DraftFlag = 1 or ra_DraftFlag = 2)
'
select @WhereSQL = @WhereSQL + @ra_draftflagSQL
--empty summary info table
delete SummaryInfoIndustrial
select @SQLCode = 'select distinct rqserno, rqpropno, estate, street,
occupier, Rent,
TotalM2, termYears, termMonths, datefixed, eaves,
ra_BMID
from vi_MAItems '
select @GroupBySQL = ' group by rqserno, rqpropno, estate, street,
occupier, Rent,
TotalM2, termYears, termMonths, datefixed, eaves,
ra_BMID
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @GroupBySQL
insert into SummaryInfoIndustrial
(
RequestNo, PropNo, Estate, Street, Occupier, Rent, TotalM2,
TermYears, TermMonths, DateFixed, Eaves,
Benchmark
)
exec(@SQLCode)
----------------------------------Temp table for calculating office
sums------------------------------
select @SQLCode = 'select rqpropno, sum(distinct val_m2) as OfficeM2
from vi_MAItems '
select @GroupBySQL = 'group by rqpropno, val_lvl
order by rqpropno'
select @OfficeSQL = ' and (val_newuse = 26 or val_newuse = 95) '
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @OfficeSQL + @GroupBySQL
create table #TempOfficeSums (rqpropno int, sumOfficeValM2 int)
insert into #TempOfficeSums
(
rqpropno, sumOfficeValM2
)
exec(@SQLCode)
--get sums of sums from tempofficesums
select @SQLCode = 'select distinct rqpropno, sum(sumOfficeValM2)
from #TempOfficeSums '
select @GroupBySQL = 'group by rqpropno '
select @SQLCode = @SQLCode + @GroupBySQL
create table #OfficeAvgs
(
rqpropno int, OfficeM2 int
)
insert into #OfficeAvgs
(
rqpropno, OfficeM2
)
exec(@SQLCode)
drop table #TempOfficeSums
--------------------------------- count of Office values per
property-----------------------------------------------
select @SQLCode = 'select rqpropno, count(val_m2) as OfficeCount
from vi_MAItems '
select @GroupBySQL = 'group by rqpropno
order by rqpropno'
select @OfficeSQL = ' and (val_newuse = 26 or val_newuse = 95) '
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @OfficeSQL + @GroupBySQL
create table #OfficeValsCount
(
rqpropno int, OfficeCount int
)
insert into #OfficeValsCount
(
rqpropno, OfficeCount
)
exec(@SQLCode)
--warehouse averages
select @SQLCode = 'select distinct rqpropno, sum(distinct val_m2)
from vi_MAItems '
--select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39
or val_newuse = 14 or val_newuse = 35 or val_newuse = 10) '
select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39 or
val_newuse = 14 or val_newuse = 35 or val_newuse = 10) and val_lvl
<> ''' + 'MEZZ'' '
select @GroupBySQL = 'group by rqpropno, estate, street, occupier,
Rent,
datefixed
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @WarehouseSQL + @GroupBySQL
create table #WarehouseAvgs
(
rqpropno int, WarehouseM2 int
)
insert into #WarehouseAvgs
(
rqpropno, WarehouseM2
)
exec(@SQLCode)
--------------------------------- count of Warehouse values per
property-----------------------------------------------
select @SQLCode = 'select count(val_m2) as WarehouseCount, rqpropno
from vi_MAItems '
--select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39
or val_newuse = 14 or val_newuse = 35 or val_newuse = 10) '
select @WarehouseSQL = ' and (val_newuse = 38 or val_newuse = 39 or
val_newuse = 14 or val_newuse = 35 or val_newuse = 10) and val_lvl
<> ''' + 'MEZZ'' '
select @GroupBySQL = 'group by rqpropno
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @WarehouseSQL + @GroupBySQL
create table #WarehouseValsCount
(
rqpropno int, WarehouseCount int
)
insert into #WarehouseValsCount
(
rqpropno, WarehouseCount
)
exec(@SQLCode)
select @SQLCode = 'select distinct rqpropno, val_topRentM2 from
vi_MAItems '
select @GroupBySQL = 'group by rqpropno, estate, street, occupier,
val_topRentM2,
datefixed
order by rqpropno'
select @SQLCode = @SQLCode + @WhereSQL + @CAVDraftFlagSQL +
@ValDraftFlagSQL + @GroupBySQL
create table #val_topRentM2
(
rqpropno int, val_topRentM2 money
)
insert into #val_topRentM2
(
rqpropno, val_topRentM2
)
exec(@SQLCode)
--objectIDs
select FileRef as rqpropno, objectID
into #ObjectIDs
from FileDB.dbo.files F
where FileRef in (select PropNo from SummaryInfoIndustrial)
and F.DocType = 4
and F.img_SetMain = 1
--Other column
select PropNo as rqpropno, BMID
into #BMIDs
from BMInformers
where PropNo in (select PropNo from SummaryInfoIndustrial)
--Insert values into summary info
update SummaryInfoIndustrial
set SummaryInfoIndustrial.OfficeM2 = #OfficeAvgs.OfficeM2,
SummaryInfoIndustrial.OfficeCount = #OfficeValsCount.OfficeCount,
SummaryInfoIndustrial.WarehouseM2 = #WarehouseAvgs.WarehouseM2,
SummaryInfoIndustrial.WarehouseCount =
#WarehouseValsCount.WarehouseCount,
SummaryInfoIndustrial.ObjectID = #ObjectIDs.ObjectID,
SummaryInfoIndustrial.Other = #BMIDs.BMID,
SummaryInfoIndustrial.val_topRentM2 = #val_topRentM2.val_topRentM2
from SummaryInfoIndustrial
left outer join #OfficeAvgs on #OfficeAvgs.rqpropno = PropNo
left outer join #OfficeValsCount on #OfficeValsCount.rqpropno = PropNo
left outer join #WarehouseAvgs on #WarehouseAvgs.rqpropno = PropNo
left outer join #WarehouseValsCount on #WarehouseValsCount.rqpropno =
PropNo
left outer join #ObjectIDs on #ObjectIDs.rqpropno = PropNo
left outer join #BMIDs on #BMIDs.rqpropno = PropNo
left outer join #val_topRentM2 on #val_topRentM2.rqpropno = PropNo
--drop temp tables
drop table #OfficeAvgs
drop table #OfficeValsCount
drop table #WarehouseAvgs
drop table #ObjectIDs
drop table #WarehouseValsCount
drop table #BMIDs
drop table #val_topRentM2
--drop table #CalculatedTotalM2
--drop table #RentAvgs
select * from SummaryInfoIndustrial
select @NumberOfRows = @@RowCount
select @NumberOfRows output
--drop table #SummaryInfo
return @SUCCESS
end --End of procedure