Faster Than Cursor?
Apr 8, 2008
I have a cursor prcedure that is pretty slow because as the cursor moves through the data I have three select statement on the same table to find other rows information. Is there a better way to do this?
Simple Example of Code is:
DECLARE MyVARABLES 1 to X
DECLARE c1 CURSOR
FOR
SELECT MyData1, MyData2 to X
FROM MyTable
FOR UPDATE OF MyUpdateData
--Start Cursor
OPEN c1
FETCH NEXT FROM c1
INTO MyVariables
--LOOP
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------
-- Get other rows data to add to this rows data ......GUESSING THIS IS THE SLOW PART as the table is LARGESELECT MyVar1 = MyData1
FROM MyTable
WHERE MyTableColumns = MyVariables AND MyTableColumns2 <> MyVariables2 --FINDS OTHER ROW (I have three of these)
--Calculate & Update
If MyVarable = 'this or that'
BEGIN
UPDATE MyTable
SET MyUpdateData = MyVar1 * x *y WHERE CURRENT OF c1
END
-------------------
-- NextFETCH NEXT FROM c1
INTO MyVarables 1 to xEND
CLOSE c1
DEALLOCATE c1
View 2 Replies
ADVERTISEMENT
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
           for select name, salary from ag
 open ll
            fetch from ll
Â
             while @@FETCH_STATUS=0
              fetch from ll
               update ag set salary=200 where 1=1
Â
  close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
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 7 Replies
View Related
Sep 25, 2007
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 1 Replies
View Related
Mar 28, 2007
hi all, if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks!
View 6 Replies
View Related
Jul 27, 2001
i have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???
thanks
kim
View 2 Replies
View Related
Jan 16, 2006
I was just wondering if this can be done any faster? code-wise that is...
Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.
UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
MATCH_basistabel.nationalitet = convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati onsnummer),
MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
MATCH_basistabel.franavision = 1
FROM MATCH_basistabel, ALL_tbl_medlemsinfo
WHERE isnumeric(matchfelt) = 1
AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer)
AND MATCH_basistabel.franavision = 0)
View 14 Replies
View Related
Feb 17, 2006
Hi Guys,
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
Many Thanks,
Regards,
Shaffiq
View 6 Replies
View Related
Jul 20, 2005
I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.
View 3 Replies
View Related
May 7, 2007
I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).
Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.
USE [TNS]
GO
/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Unique_Login_IPs]
(
@year1 int,
@year2 int
)
AS
BEGIN
SET NOCOUNT OFF;
-- Define the years for testing purposes
set @year1 = 2006
set @year2 = 2007
SELECT month,[2006] as y2006,[2007] as y2007
FROM
(
SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1
UNION
SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2
UNION
SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3
UNION
SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4
UNION
SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5
UNION
SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6
UNION
SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7
UNION
SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8
UNION
SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9
UNION
SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10
UNION
SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11
UNION
SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12
UNION
SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1
UNION
SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2
UNION
SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3
UNION
SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4
UNION
SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5
UNION
SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6
UNION
SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7
UNION
SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8
UNION
SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9
UNION
SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10
UNION
SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11
UNION
SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12
) piv
PIVOT
(
SUM(cnt)
FOR year IN
([2006],[2007])
) as child
END
View 1 Replies
View Related
May 14, 2007
Hi all, I m new to this forum and this is my first question. I m having 2 pages in my web site ... page 1 query directly to db using sqldatasource, the second page query through a BLL then DAL by following the step in this tutorial (http://www.asp.net/learn/dataaccess/tutorial02vb.aspx?tabid=63).... Page 1 is using a "Like" query search and the Page 2 is the normal displaying some product detail.... Under normal circumstances, one will expect Page 1 will be way fastest than the Page 2... however the problem is Page 1 is in thunder speed while Page 2 takes 10 secs to load... 10 seconds is really not acceptable... I really couldnt figure out what happens... both Page 1 and Page 2 are using the same connection string which connection through a DSN.... How is the connection different by using sqldatasource and DAL?? Could someone please help.... ThanksP.S. I m using a Pervasive database
View 5 Replies
View Related
Jun 20, 2007
Hi y'all,
I've recently run a profiler on my code and following query took 7 seconds:
SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC
Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table?
Thanks in advance! I'll let you know how long your query takes :)
View 4 Replies
View Related
Mar 31, 2004
Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????
Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???
Question 2:Can anybody give me few examples like this?????? I need them desparetly.
Thanx. Bye.
View 37 Replies
View Related
May 18, 2004
I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better?
public class DataTransaction
{
private bool blnError = false;
private ArrayList arrErrorList = new ArrayList();
private SqlConnectionobjConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
private SqlTransactionobjTransaction;
public DataTransaction()
{
objConnection.Open();
objTransaction = objConnection.BeginTransaction();
}
~DataTransaction()
{
if(objTransaction.Connection != null)
{
objTransaction = null;
objConnection.Close();
}
}
public int ExecuteNonQuery(string Query)
{
int intRowsAffected = -1;
SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);
try
{
intRowsAffected = objCommand.ExecuteNonQuery();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return intRowsAffected;
}
public System.Object ExecuteScalar(string Query)
{
System.Object objToReturn = null;
SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);
try
{
objToReturn = objCommand.ExecuteScalar();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return objToReturn;
}
public bool Commit()
{
if(!blnError)
{
objTransaction.Commit();
return true;
}
else
{
objTransaction.Rollback();
return false;
}
objConnection.Close();
}
}
View 2 Replies
View Related
Dec 20, 2004
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks
View 7 Replies
View Related
Jul 26, 2006
I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.
The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.
If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.
Is there anything I can do?
View 5 Replies
View Related
Jun 17, 2004
Hypothetically,
If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?
An example:
WHERE a = 'abc'
OR a = 'def'
OR a = 'ghi'
as opposed to:
WHERE a = 'abcdefghi'
ddave
View 2 Replies
View Related
Dec 20, 2004
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks
View 2 Replies
View Related
Feb 23, 2006
A little background on my database structure:
I have a live database and an archive database. I update the archive tables once a day from the live tables using:
INSERT INTO arc_table
SELECT * FROM cur_table AS cur
WHERE NOT EXISTS
(SELECT * FROM arc_table AS arc
WHERE arc.key = cur.key)
GO
This inserts newer records into the archive tables from the live tables.
I have two different methods to clean the live tables once a week but keep data from the previous week. Both methods have been verified to delete the same rows.
DELETE cur_table
WHERE EXISTS
(SELECT key FROM arc_table AS arc
WHERE arc.key = cur_table.key)
AND date_time < GetDate() - 7
GO
Second method modified from BOL - deletes identical rows
DELETE cur_table
FROM (SELECT key FROM arc_table) AS arc
WHERE arc.key = cur_table.key
AND date_time < GetDate() - 7
GO
I read that "WHERE [NOT] EXISTS" is faster than "WHERE [NOT] IN" but this is the first time I have seen DELETE xx FROM (SELECT ----)
I'd like to know which procedure will be faster and/or better.
View 1 Replies
View Related
Sep 26, 2006
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
View 14 Replies
View Related
Apr 17, 2008
I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster?
Thanks for your input.
View 4 Replies
View Related
Oct 17, 2007
Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
View 4 Replies
View Related
Mar 19, 2004
Here is he issue.
* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper.
* In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT.
* The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.
I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.
Alternative:
Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.
With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.
Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.
Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.
Any comments on this?
View 3 Replies
View Related
Jan 21, 2005
I have a situation where currently, I check to see if a record exists and I do an update if it does, I do an INSERT if it does not. This happens about 200k times each night.
I'm trying to speed the application up that is doing this and I was wondering if it would be a better idea to just delete the records I'm about to recreate, before I start, then do an unconditional insert.
It would eliminate my IF EXISTS check but if an update is faster than an insert, it might even out.
Also, will SQL deal with the big delete and the data being recreated each night without too much bloating or fragmentation?
View 2 Replies
View Related
Aug 29, 2005
File I/O or SQL Database calls?Note that my SQL Server is on a separate server, so that cuts down on the web server resources needed per query, but it increases the time necessary for the query to happen.
View 1 Replies
View Related
Mar 30, 2006
Does using varchar or varchar(max) affect the system performance?
Should I use fixed size coloumns to increase speed?
View 4 Replies
View Related
Jan 26, 2001
Hi.We have stored procedure update specific table
Each time it run it delete 5000- 6000 rows
from table then insert 5000- 6000 rows with different information.
It take up to 1 1/2 min execute.
1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ?
2. Is any other way increase speed of insert?
View 2 Replies
View Related
Dec 9, 1998
We intend to import up to 20+ GB of data into a database that will be a snapshot of month end data. The database tables must be dropped and then recreated just prior to importing the data. We are investigating BCP, DTS, and Bulk Insert. What is the fastest method for importing data given our approach?
View 1 Replies
View Related
Dec 16, 2005
Hello friends,
Kindly guide, if you are aware of any mechanism for restoring the differential backup only.
This can save a lot of time other than restoring complete database.
Thanks in advance.
Shrirang
View 2 Replies
View Related
Feb 21, 1999
Hello:
We have been testing a new vendor_purchased application and are now
running some month-end processes/jobs now. One of the processes that we
kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini
g. In reviewing what happened, I noticed that tempdb is in the process of
clearing itself out slowly when we restarted the process. The database has
80% of its disk space remaining and the transaction log has also 75%
remaining.
My questions are:
1) Although I don't think so, is there nay way of speeding up the process
of tempdb clearing out the data in it?
2) We will need to examine what sql code the vendor has used that caused
this to happen. Aside from group by and order by, if there are a lot of
'select into' code, what alternatives do we have?
Any information that can be provided will be fine. THanks in advance.
DAvid Spaisman
View 1 Replies
View Related
Mar 20, 2007
I need help makeing the following query run more efficently.
Code:
SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state
View 3 Replies
View Related