Loop Which taking so much time...

[sqldevrequiretektips]

Dim rsDowload As ADODB.Recordset

clsMvSQLServerSP.CreateStoredProcedure sqlDB ( This Method Executed for creating Storeprocedure and sqlDB it's string variable which has some where clause long expression )

clsMvSQLServerSP.ExecuteStroedProcedure rsDowload
(This Method Executed for execute the store procedure)

after Above steps done i have all the records which i needed in rsdowload(Recordset)

Now i have table name tblWIActCat_WIday which is local Access table and I trying to insert all this records from rsdownload (recordset)...


If Not (rsdownload.BOF And rsdownload.EOF) Then rsdownload.MoveFirst
Do While Not rsDowload.EOF
strSQL = "Insert into tblWIActCat_WIday(CustLifeNo,WIActCatId,WIDayId,WeekNo) Values (" & "'" & rsDowload.Fields(0).value & "'" & "," & "'" & rsDowload.Fields(1).value & "'" & "," & "'" & rsDowload.Fields(2).value & "'" & "," & "'" & rsDowload.Fields(3).value & "'" & ")"
scon.Execute strSQL, , adCmdText + adExecuteNoRecords
rsDowload.MoveNext

Loop

Above code taking so long to complete the loop cause rsdoload (recordset) has more then 30000 records,

Is any body give me better solution where i can improve the perfomance...

What's quickest way to insert the records in table from recordset.......

Any help would really appriciate...
Thanks
AD

 

[chiph]

You can gain a big performance boost by changing to parameterized queries. Do a search here or in the VB6 forum under "SQL Injection", and you should find some examples.

What parameterized queries do for you (vs. just jamming some single-quotes on your values) is it allows the database to cache your query. Without them, it must evaluate your query for correctness every time you run it.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sqldevrequiretektips]

That's what i thought and here what i did but somereason i am getting an errror...

If you could please help me out with syntex would be great..


CREATE PROCEDURE terrredesign.RetriveDownloadData
@strWhere varchar(8000) -- This is the input parameter.
AS

Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A
Inner Join (select distinct (cust_life_num) from CUSTTB where " & @strWhere & " )B
On A.CustLifeNo = B.Cust_Life_num

Go


@strWhere It's string...
Value will be
@StrWhere = Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)

Now could you please help me out what's wrong with this..
I am getting an error "Line 8: Incorrect syntax near ')'."

 

[MarkSweetland]

You can't concatenate the SQL statement at the point of execution. Again, not recommended but you can use the exec() method:

CREATE dbo.spsomeUpdate
@str_tbl_name varchar(30)
AS

DECLARE @str_sql1 varchar(255)

select @str_sql1 = "update " + @str_tbl_name + " set somecolumnName = 'aValue'

exec(@str_sql1)



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[sqldevrequiretektips]

Thnaks, for your reply...
After see your reply i have modify code in my store proc.
but i am getting an errors..

*** Errors***
Line 6: Incorrect syntax near '@str_sql'.
The identifier that starts with ' Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A
Inner Join (select distinct (cust_life_num)' is too long. Maximum length is 128.

Must declare the variable '@str_sql'.

*****

CREATE PROCEDURE terrredesign.RetriveDownloadData
@strWhere varchar(8000) -- This is the input parameter.
AS
DECLARE @str_sql varchar(2000)

@str_sql = " Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A
Inner Join (select distinct (cust_life_num) from CUSTTB where " + @strWhere + " )B
On A.CustLifeNo = B.Cust_Life_num"

exec(@str_sql)

--Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
--from WLActCat_WLDay A
--Inner Join (select distinct (cust_life_num) from CUSTTB where " & @strWhere & " )B
--On A.CustLifeNo = B.Cust_Life_num

Go

If you could please help me out with syntax would be really appriciate.. i am really new with store proc so, please help me out....

Thx
AD

 

[MarkSweetland]

You're missing an initial opening paren and use single quotes for strings...


declare @strWhere varchar(8000) -- This is the input parameter.

DECLARE @str_sql varchar(2000)

select @StrWhere = [red]'([/red]Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)[red]'[/red]
select @str_sql = [red]'[/red]Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo from WLActCat_WLDay A Inner Join (select distinct (cust_life_num) from CUSTTB where [red]'[/red] + @strWhere + [red]'[/red] )B On A.CustLifeNo = B.Cust_Life_num[red]'[/red]

select @str_sql



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[sqldevrequiretektips]

Thanks for your reply...

please Look my code... Still i am getting an error on line
set sql2 " Incorrect syntax near '='."

Please help me out to solve this error


CREATE PROCEDURE [terrredesign].[P_Retrive_Dowload_data]
(@strWhere varchar(8000))AS
SET NOCOUNT ON
DECLARE @Error AS Integer

DECLARE @sql AS varchar(8000)
DECLARE @sql1 as varchar(8000)
DECLARE @sql2 as varchar(8000)

set @sql1 = 'Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A Inner Join '

set sql2 = ' (select distinct (cust_life_num) from CUSTTB where ' + @strWhere + ') B
On A.CustLifeNo = B.Cust_Life_num '

set sql = @sql1 + @ sql2

print @sql2

Go

 

[sqldevrequiretektips]

Thanks for your reply...

please Look my code... Still i am getting an error on line
set sql2 " Incorrect syntax near '='."

Please help me out to solve this error


CREATE PROCEDURE [terrredesign].[P_Retrive_Dowload_data]
(@strWhere varchar(8000))AS
SET NOCOUNT ON
DECLARE @Error AS Integer

DECLARE @sql AS varchar(8000)
DECLARE @sql1 as varchar(8000)
DECLARE @sql2 as varchar(8000)

set @sql1 = 'Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A Inner Join '

set sql2 = ' (select distinct (cust_life_num) from CUSTTB where ' + @strWhere + ') B
On A.CustLifeNo = B.Cust_Life_num '

set sql = @sql1 + @ sql2

print @sql

Go

 

[MarkSweetland]

set [red]@[/red]sql2 = ' (select distinct (cust_life_num) from CUSTTB where ' + @strWhere + ') B
On A.CustLifeNo = B.Cust_Life_num '

set [red]@[/red]sql = @sql1 + @sql2



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[chiph]

I think you're missing the point. Instead of building up SQL in some variables and running it via the exec method, try something like this:

CREATE PROCEDURE [terrredesign].[P_Retrive_Dowload_data]
    (@strWhereVal varchar(8000)) AS
SET NOCOUNT ON

SELECT A.Cust_Life_Num, 
   A.WLActCatId, 
   A.WLDayId, 
   A.WeekNo
FROM WLActCat_WLDay A 
  INNER JOIN (SELECT DISTINCT (cust_life_num) 
      FROM CUSTTB 
      WHERE CUSTTB.SomeColumn = @strWhereVal) B
  ON A.CustLifeNo = B.Cust_Life_num

This stored procedure returns a rowset consisting of Cust_Life_Num, WLActCatId, WLDayId, and WeekNo. It gets compiled and stored in SQLServer's procedure cache, so you get good performance out of it.

Using Exec negates any performance benefit because it causes SQLServer to evaluate what you send it every time (what you're trying to avoid).

If you need more where-clause conditions other than just a check against "SomeColumn", you would add them to the stored proc. If you need some dynamic where-clause conditions (like where the user can specify what they want brought back), you need to restrict what they can choose, and write a seperate SP for each (or a monster SP with some if..then checks), or give up on the idea of increasing performance.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first