Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Cursor Versus While Loop
I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.

Thanks!!

View Replies !

---

Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.

View Replies !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

---

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

---

Dynamic SQL In Cursor
I need to pass a list of values into a cursor as such...


DECLARE
@group_SQL varchar(255)

SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')'

DECLARE groupContact_import_cursor CURSOR
FOR EXEC(@group_SQL)
OPEN groupContact_import_cursor
FETCH NEXT FROM groupContact_import_cursor INTO @group_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid)
FETCH NEXT FROM groupContact_import_cursor INTO @group_id
END
CLOSE groupContact_import_cursor
DEALLOCATE groupContact_import_cursor

But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light?

TIA

View Replies !

---

Dynamic Cursor
I am trying to use a dynamic cursor in a stored procedure:
The code looks like this :


/************************************************** ***
set @sFormula = 'Monthlyformula'
set @sStartDate = '02/01/2004'
set @sEndDate = '02/01/2004'


exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' )
/************************************************** ***

And this is what it is interpreting

select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004)

My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned

Thanks in advance

SK

View Replies !

---

Dynamic Cursor
Hello !

I´m having a big problem with a dynamic cursor.
There is my problem:

I have two cursors. One I use to select a master table. The other I use to select a detail table. I want to fetch all rows of the master and for each row select the details.

How can I pass to the detail cursor the master key ?

Thanks and I´m sorry for my poor English ! :)

View Replies !

---

Dynamic Cursor
Hi All€¦
 
I need to bind a DataGrid to server dynamic cursor.
 
Please help!

View Replies !

---

How Get The Dynamic Sql In To Cursor
Dear folks,

In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation.

Declare @sql varchar(5000);
set @sql='select * from Role where Role_id in('+ @role_ids +')';
Exec @sql;

I want take this record set in to cursor .How to do it.
please help me.

View Replies !

---

Dynamic Cursor Generation..
Hi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee

View Replies !

---

Declare Cursor With Dynamic SQL?
Hello..

Can you declare a cursor with dynamic SQL?

I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.

these attempts did not work:

DECLARE crsCursor CURSOR FOR @vchrSQL
DECLARE crsCursor CURSOR FOR (@vchrSQL)

Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?

View Replies !

---

Dynamic Select For CURSOR
Hi all

I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID

etc etc

Here's the cursor


DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod

OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1

-- DO SOME FUNKY STUFF


FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID

View Replies !

---

Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View Replies !

---

Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas

View Replies !

---

Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time.

Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work.

e.g.

declare @A varchar(10),
@B varchar(10),
@sFetch varchar(255)

--Open the cursor
Execute ('Open ' + crAnyCursor)

--create fetch string
Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B'

execute (@sFetch)

The following error is returned...
Msg 137, Level 15, State 1
Must declare variable '@A'.

Is there another means of working with dynamically executed cursors?

Thanks
JohnG

View Replies !

---

Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot.

CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS
declare

@cmdtxt as varchar(4000),
@SQLString NVARCHAR(4000),
@SQLString1 NVARCHAR(4000),
@pid varchar(22),
@lname varchar(60)
begin

EXEC SQL BEGIN DECLARE SECTION;

char prep[] = @p_query;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE prep_stat FROM :prep;

EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat;



OPEN contact_crsr
FETCH NEXT FROM contact_crsr
INTO @pid, @lname


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname
select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt"
exec master..xp_cmdshell @cmdtxt

FETCH NEXT FROM contact_crsr

INTO @pid, @lname

END



CLOSE contact_crsr

DEALLOCATE contact_crsr


end

View Replies !

---

Cursor Performance Question
Hey,



I have a stored procedure below that loops through a table and updates another table. I used to do this client side in vb but was hoping for better performance when doing everything server side.

Question;



Am I doing something wrng since the query is that slow.?
Why is cursors so bad ?


Thanks



David





ALTER PROCEDURE usp_setinternational

AS

Declare @acnt_no int

declare @cntry_code varchar(10)

declare @counter int

declare @error int

declare MyC cursor

Local

for

SELECT ACNT_NO, COUNTRY_CODE FROM INTNL_ACNT

open MyC

fetch next from MyC into @acnt_no,@cntry_code

select @counter = 1

while @@fetch_status = 0

begin

update xform2 set

address_type='m'

where acnt_no = @acnt_no

update addr set delivery='m',cntry_cde= @cntry_code

where addr_id in (select addr_id from acnt_addr where acnt_no = @acnt_no)

select @counter = @counter +1

fetch next from MyC into @acnt_no,@cntry_code

end

View Replies !

---

Cursor Will Increase Performance Or Not
"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".

(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?

Srinivasan

View Replies !

---

Problem With Cursor Performance
hi friends i am using a cursors to fetch data row by row , but the main problem is it takes a very big time span to execute can anybody help me out to replace cursor with some other method or trick so that i can make it perform fast... see my query is like this:

--update cdr set destinationid = null

DECLARE @CDRID BIGINT;

DECLARE @CodeDestID BIGINT;

DECLARE @TempCodeDestID BIGINT;

DECLARE @CtryCode VARCHAR(20);

DECLARE @MATCH INT;

DECLARE @dialed_digits NVARCHAR(50);
DECLARE @FormattedNbr VARCHAR(50);
DECLARE @CountryCode VARCHAR(2);
DECLARE CDR_cursor CURSOR FOR SELECT CDRID, dialed_digits FROM mydata.dbo.CDR WHERE DestinationID IS NULL
OPEN CDR_cursor

FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

WHILE @@FETCH_STATUS = 0

      BEGIN

            SET @FormattedNbr = replace(LTRIM(RTRIM(@dialed_digits)), '"', '');

            SET @CountryCode = substring(@FormattedNbr, 0, 3);

SET @CodeDestID = null;

            SET @MATCH = 0;

 DECLARE Dest_cursor CURSOR FOR SELECT DestinationID, Code FROM mydata.dbo.ctryCode WHERE code Like LTRIM(RTRIM(@CountryCode)) +'%' Order by code Desc

            OPEN Dest_cursor

 FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode
 
WHILE @@FETCH_STATUS = 0

                  BEGIN

            IF @MATCH <> 1

                        BEGIN

                              SET @MATCH =  PATINDEX(@CtryCode + '%', @FormattedNbr);

                              IF @MATCH = 1

                                    BEGIN

                                          print @TempCodeDestID

                                          SET @CodeDestID = @TempCodeDestID

                                   END
                        END
 
                  FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode

            END
 
            CLOSE Dest_cursor

            DEALLOCATE Dest_cursor

IF @MATCH = 1

                  BEGIN

                  --Updating the maximum possible match

                  UPDATE mydata.dbo.CDR SET DestinationID = @CodeDestID WHERE CDRID = @CDRID

                  SET @CodeDestID = null;

                  SET @MATCH = 0;
 
                  END
FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

END

CLOSE CDR_cursor

DEALLOCATE CDR_cursor


SELECT     mydata.dbo.CDR.CDRID, mydata.dbo.CDR.start_date_time, mydata.dbo.CDR.ani, mydata.dbo.CDR.dialed_digits, mydata.dbo.CDR.actual_dur, mydata.dbo.CDR.rounded_dur_secs,mydata.dbo.CDR.cost, mydata.dbo.CtryCode.Destination, mydata.dbo.CtryCode.Code,

                      mydata.dbo.CtryCode.Cost AS Code_Rate

FROM         mydata.dbo.CDR LEFT OUTER JOIN

                      mydata.dbo.CtryCode ON mydata.dbo.CDR.DestinationID = mydata.dbo.CtryCode.DestinationID


please help me its urgent.....

View Replies !

---

Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James



-- SQL ---------------

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;



--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.

View Replies !

---

Executing SP Having A Dynamic Cursor Fails In Calling SP
Hi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:

Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.

Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !

Thanks: Peter

View Replies !

---

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

 

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

 

This code is not working.

 

please suggest

 

Nitin

View Replies !

---

Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and  I need make a cursor with  a contents of this fileds.

How can I do ???

My code:

Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat

Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
  @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)

Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
  SELECT Local, Linha, Campo
  From Relatórios_Margens
  WHERE (idRelat = @idRelat)
  ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
  If @a_Local = "C"
    Select @Tot_Col = @Tot_Col + 1
  Else
    Select @Tot_Lin = @Tot_Lin + 1
  Select @Campos = @Campos + 1
  If @Aux_Group <> "GROUP BY " begin 
    Select @Aux_Group = @Aux_Group + ", "
  If @Aux_Order <> "ORDER BY " begin 
    Select @Aux_Order = @Aux_Order + ", "
  Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
  Select @Aux_Group = @Aux_Group + @a_Campo
  Select @Aux_Order = @Aux_Order + @a_Campo
  Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux

Not working !!!!

 

View Replies !

---

Dynamic Query, Local Cursor Variable And Global Cursors
Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View Replies !

---

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

---

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !

---

Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View Replies !

---

Extremely Poor Query Performance - Identical DBs Different Performance
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***

View Replies !

---

Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper

View Replies !

---

Very Poor Performance - Identical DBs But Different Performance
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T

View Replies !

---

Poor Performance
Hello Gurus,SQL Server 2000Windows 2003 Server, Standard Edition.Firstly I'm not a SQL server DBA but have a little experience withOracle 9i and Oracle Rdb.An application that I've inherited has started performing very veryslowly over the last few days, as far as I know there have been nomodifications or changes in the volume of data the db is holding. Thedatabase is in simple logging mode and it's updated twice per day fromand Oracle Rdb database.The SQL database is all in the primary filegroup and is sat on fourdisks (RAID mirrored) which form one logical disk of 128gb. Itconsists of approx 120 tables, each with a primary key and a number ofunique indicies per table.Apart from a twice a day update in the small hours the only access tothis database is read only. Users are connecting via the web andusing predefined Crystal reports to retrieve data.Questions then:Should this db have more than one filegroup and should I put theindicies in a different group? Is this relevant when the underlyingstorage is RAID?Should it be using mirrored RAID or should it be striped or should Isteer clear of RAID for a database?The indicies have not been rebuilt for some time (probably one year).Is this something I should be doing once per day/week as I do have thedowntime window?Should I be doing a database/table reorg at fairly regular intervals?I'm going to generate a workload file from SQL profiler and see what,in conjunction with the Index tuning wizard it suggests.Appreciate that poor performance & tuning is a huge subject and not anexact science but any tips or comments would be gratefully received.RegardsDave.

View Replies !

---

SQL 7 Poor Performance
Hi,
I have just upgrade my sql 6.5 to 7.0 version. This sql box (compaq proliant 5500) has 1 gig ram and 4 pentium pro processors and smart array controller. There are about 200 users hit on that server constantly. I configured this server according compaq white paper and Microsoft recommendation; however, I am still suffering huge performance hit. Then I setup performance monitor to see what is happening. What I see is all of 4 processors are at 70% processor time constantly. What I heard is sql 7.0 runs much better on pentium III processors. Is that true? Or you have any recommendations??


Help!!
Thanks!!

Jim Zhong

View Replies !

---

Poor Performance
Hi
 
I have the following structure:
 
At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.
 
This is my problem:
 
The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.
 
I would like to know what problem can be cause this delay.
 
Thanks

View Replies !

---

Poor Performance
Is there a way to improve this query?
The execution plan states that it's ,aking use of the Indexes as 'Clustered Index Seeks'
but the query takes 30 minutes to complete.
The Index statistics are also up to date!
If I use just an INNER JOIN the query completes in 2 seconds!
How can I make the LEFT OUTER JOIN more optimal?
SELECT A.MeterSerial AS 'PeaceMeter', B.MeterFrom, B.MeterTo,
  CASE WHEN A.MeterSerial = B.MeterFrom
     OR A.MeterSerial = B.MeterTo
     OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
      THEN 'Y' ELSE 'N' END AS 'ComplexMeter', B.ComplexMeterType
FROM Peace.MeterData_DR1 A LEFT OUTER JOIN
   (SELECT MeterFrom, MeterTo, ComplexMeterType
    FROM Peace.ComplexMeters2) B ON A.MeterSerial = B.MeterFrom
           OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
WHERE A.MeterSerial != ''

View Replies !

---

Poor Performance From Web Server
Hi,I am having a problem with one of my stored procedures in SQL Server 2005.  Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET. I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it.  More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig.  I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.If anyone has got any ideas on this then please let me know as I am completely stuck.  I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...Thanks in advance Tom    

View Replies !

---

SSAS Poor Performance
Hi,

I created a cube in my development box, tested, performance is great, now if I try to deploy it into the production server from VS, the screen freezes without any error msg... after some time playing around with security I gave up and created a backup of the cube, and sent it over to the IT department( I have no direct control over the production server so they restored the cube ). Now with the cube in production, it was a matter of just processing it... well that didn't go well either... it would take forever from my development box to see results... then I tried to browse any dimension using the data already on the backup... a simple 4 values dimension would take 2-4 minutes to load on screen...

I can't understand why browsing the cube its so slow in the production server, IT admin even reported that when trying to browse locally it would be slow too... The server has 16gbs of RAM and its a dual processor, he didnt notice any lack of CPU or memory while browsing the cube...

Have you experienced this or can you help me troubleshoot whats going on?

View Replies !

---

Poor Performance Right After Installing
Hi everybody,

We´ve just installed 2 SQL Servers on 2 different machines:

1.
- SQL 6.5 with SP5a installed on D:
- HP NetServer E45
- 192 MB RAM
- Pentium II 233
- U2W Disks
- Win NT 4 with SP4

2.
- SQL 7.0 installed on D:
- HP Netserver LD Pro
- Pentium Pro 166
- 256 MB RAM
- 4,2 GB HP SCSI Platte HOT SWAP
- Adaptec 2940
- C: 1,5 GB FAT
- D: 2,7 GB NTFS
- WIN NT 4.0 Server SP4


Both Servers react EXTREMELY slow ("feels like glue") even without any user-dbs installed on.

A simple JOIN on the pubs-db

SELECT * from title, titleauthor, employee

takes MINUTES.


The same JOIN on a third SQL 6.5-Server, installed on a
no-name-we´ll-use-this-as-a-test-server Pentium 133 (with less RAM)
is done in less than 40 seconds.

All three host NOTHING more than the pubs-db.
Configuration is left default except (of course) memory.
Tempdbs had no effect.

Anyone got any idea?
I`ve already checked the articles on the MS support sites,
but we have definitely checked memory configurations.

Thanx very much in advance for help,

Juergen

View Replies !

---

Poor Performance On Oracle
Attention: To all SSIS gods like Donald Farmer, Jamie Thompson, Michael Entin

I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.

I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.

The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!

PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!

View Replies !

---

Poor Index Performance
Hey guys,

Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :


create table Customers(
   CusID int not null,
   Name varchar(50) null,
   Surname varchar(50) null,
   CusNo int not null,
   Deleted bit not null
)


create proc spCusLs (
   @CusID int = null,
   @Name varchar(50) = null,
   @Surname varchar(50) = null,
   @CusNo int = null
)
as

select
   CusID,
   Name,
   Surname,
   CusNo
from
   Customers
where
   Deleted = 0
   and CusID <> 1000
   and (@CusID is null or CusID = @CusID)
   and (@CusNo is null or CusNo = @CusNo)
   and (@Name is null or Name like @Name)
   and (@Surname is null or Surname like @Surname)
order by
   Name,
   Surname


create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary



I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.

What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...

View Replies !

---

Poor Performance Due To LCK_M_S?
We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?

Here is a snippet of code I used to test the speed against 2000:

declare @counter int
set @counter = 1

while @counter < 200
BEGIN
update UPR40500 set actindx = 96 where actindx = 96
set @counter = @counter+1
END

This takes 15 seconds when it used to take virtually no time at all.

View Replies !

---

Linked Server = Poor Performance
Using a query through a linked server is giving tremendously reduced performance.
Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?
Are there any other tips out there?
Thanks.

View Replies !

---

Poor Performance Over Satellite Link
I have a SQL server database in Riverside CA and a VB front end accessing it using rdo via ODBC in Costa Rica. The connection is via a satellite link. The users report it takes 100 seconds to complete a single, simple INSERT statement. Other remote users with land lines such as in Utah enjoy almost instantaneous reponse times.

The satellite link is 19.2kB but takes a minimum of 2 seconds for any message. The Utah connection is 56kB with no perceptable delay.

What options do I have for improving response times with this connection? At this time, there are no commercial land lines into Costa Rica.

Any thoughts would be appreciated.

Thanks

View Replies !

---

Merge Join's Poor Performance
Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?



 

View Replies !

---

Poor Sql Performance In Oledb Source?
i need to select data by using a very complex sql statement. when i use a ole db source componente and choose SQL command as data access mode the process never ends. but when i put the sql statement in an sql task component it works fine and fast. isn't an oledb source always based on an sql statement (select *)? so how is it possible that this component becomes so slow?

View Replies !

---

Poor Performance On Sql 2005 Vs. Sql 2000 - AGAIN!
I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.
 
I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.
 
1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.
 
I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.
 

SELECT InterimView.* ,TestView.*

FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
                                 FROM View_LabDataExport_FormData_55 ReqView
                                LEFT OUTER JOIN View_LabDataExport_FormData_CD_55  CDView
                                ON  ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )

                              ) InterimView

ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND

        InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )
 
The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.
 
Here is the statistics ion on 2005
 

(21234 row(s) affected)

Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
Here is the statistics ion on 2000

Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.

Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.

Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.

Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.

Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.

Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.

Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.

Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.

Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.

Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.

Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.

Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.

Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.

Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.

Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.

Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.

Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.

Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.

(21234 row(s) affected)

(147 row(s) affected)
 

One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.
 
What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.
 
Thanks

View Replies !

---