vb.net and sql server 2005 stored procedure very slow

  • Thread starter Thread starter LuvinLunch
  • Start date Start date
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
 
Do you think the query is slow becuase I'm passing in a string?
Click to expand...

It is most likely due to not having the correct protocol on SQL Server
for .NET. Check the configuration first before pounding at the process.

If you want a more firm answer, you can also profile SQL Server (assume
this is something other than Express and Express only).
Should I pass in multiple optional parameters instead?
Click to expand...

Passing multiple parameters is faster than ripping a string, but the rip
is on the SQL side, so that is not your issue here.
Is there a
smarter way to get my joins working? I'm not sure what part of the SP
is slow.
Click to expand...

Probably the connection, rather than the SP.

Peace and Grace,


--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Hi Luvin,

We see a bunch of SQL code, that would mean that you can test it in SQL
Manager.

However, what is your VB for Net code?

The SQL code is not so relevant for this newsgroup.

Cor

LuvinLunch said:
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
Click to expand...
 
Hey Gregory and Cor,

Gregory, thanks a million for the advice. Will look at changing the
configuration of SQL Server. Great to know you don't think it's
related to the way I'm passing parameters. I appreciate the help.

Cor, fair point about the SQL. Sorry for bulking up your vb forum!
I've included the vb that I'm using to call the SP below but I don't
think that's the problem because when I called the SP from a query in
the manager, it took 27 seconds to return 208 rows. Slooww.
Nonetheless, if you've any comments on how I've called the SP I'm
interested to hear them as I'm only learnnig vb.net.

Thanks to both of you for the feedback

LL


strSQL = " where racocode = 6 AND raLAcode = 28 AND raRDcode =
3 "

strSPName = "sp_MAItems"
cmd.CommandText = strSPName
sqlconnection1.Open()
sqlconnection1.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandType = CommandType.StoredProcedure

'set up the parameters
cmd.Parameters.Add("@WhereSQL", SqlDbType.NVarChar, 3000)
cmd.Parameters("@WhereSQL").Value = strSQL
cmd.Parameters.Add("@NumberOfRows", SqlDbType.Int, 4)
cmd.Parameters("@NumberOfRows").Direction =
ParameterDirection.Output

Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd ' Execute the command
Dim ds As DataSet = New DataSet
da.Fill(ds, "Results")

DG_MAItems.DataSource = ds
DG_MAItems.DataMember = "Results"
 
Luvin,

Probably I would done it more like you (don't look to the content of the
parameters, if this works, it is something I did not know that you are able
to add a complete command string to Sql, I am common to do the where one by
one.

Try
Using sqlConnection1 As New SqlConnection("ConnectionString")
Using da As New SqlDataAdapter("sp_MAItems", sqlConnection1)
sqlConnection1.Open()
sqlConnection1.CreateCommand()
da.SelectCommand.CommandType =
CommandType.StoredProcedure
da.SelectCommand.Parameters.Add(New _
SqlParameter("@WhereSQL", " where racocode = 6 AND
raLAcode = 28 AND raRDcode = > 3 "))
da.SelectCommand.Parameters.Add(New
SqlParameter("@NumberOfRows", 4))
Dim dt As New DataTable("Results")
da.Fill(dt)
End Using
End Using
Catch ex As Exception
'Error handling here
End Try

Dim bs As New BindingSource
bs.DataSource = dt
DG_MAItems.DataSource = bs

If is of course not tested at all, but to give you an impression.

Success

Cor
 
Hey Cor,

I tried the datatable instead of the dataset and using the binding
source as well as changing how I set up the parameters but the problem
persists. Will look at the configuration of SQL next week.

Thanks a million for your help,

LL
 
Back
Top