SQL 2005 50% Slower Than 2000
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000?
Any help would be greatly appreciated.
View 1 Replies
ADVERTISEMENT
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000? Any help would be greatly appreciated.
View 20 Replies
View Related
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000? Any help would be greatly appreciated.
View 5 Replies
View Related
Jul 20, 2005
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies
View Related
Jun 27, 2001
Hi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
View 2 Replies
View Related
Mar 1, 2008
hi, friends, please look at this:
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records.
2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is:
I run the package in the SQL SERVER 2005 Store Procedue like this:
do
dtexec.exe package.dtsx
loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower.
the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why?
The package don't drop the object it create during the loop in the Store Procedue ?
Thanks!
View 11 Replies
View Related
Feb 6, 2008
Hi all,
My ASP application is running fast in SQL Server 2000. But when we shifted it to SQL Server 2005, it becomes very slow.
I have created the indexes also. There is lot of data in the tables.
Is there any option to build the indexes for previous data.
Please help....
-Shah.
View 2 Replies
View Related
Jul 20, 2007
We have an extensive stress test for our OLTP application.that runs slower after upgrading to SQL Server 2005.
We have done the following:
- applied SP1 and SP2
- updated statistics (sp_updatestats)
- recompiled all stored procedures
Some behavior we notice in Perfmon over the course of the 1-hour test is:
- Average CPU Utilization is up from 22% to 32% and we're trying to figure out why
- Compilations per second are up from 200 to 250
- Recompilations per second are down from 5 to 1 (which is good).
- Memory utilization is identical.
- We see exagerated spikes in response times. The general performance
seems pretty good - but there are periodic spikes that hurt our averages.
- I examined perfmon for waits and nothing jumped out.
- A small handful of application workflows are much slower, while most are roughly identical.
I realize there could be a zillion different causes. But if anyone has experience upgrading a large OLTP solution which uses a mixture of dynamic SQL and stored procedures to SS2005 - and tuning the upgraded database - please offer any suggestions.
Our current plan includes (a) running the Best Practices analyzer which already uncovered a trace flag we may want to use since we're running on an 8-way, (b) rebuilding indexes, and (c) examine a SQL Trace this time around too since it appears the slowness is isolated to certain application workflows.
Please offer any suggestions that have worked for your team.
Thanks so much!
Mike
PS I will post all the things we end up doing in the end once we get performance equal or better - hopefully the latter!!!
View 9 Replies
View Related
Nov 16, 2006
I'm working on upgrading VB6-based application to employ SQL Server 2005 Express (it currently uses MSDE). I'm using the following connection string -
Provider=SQLNCLI;Persist Security Info=False;User ID=my_user;Password=my_password;Initial Catalog=my_database;Data Source=my_pc;Packet Size=8000
The application runs exceedingly slow. As soon as I uninstall SQL Server 2005 Express edition and install SQL Server 2005 Developers edition speed increases in 5 to 10 times. Once I uninstall SQL Server 2005 Developers edition and install SQL Server 2005 Express edition the application's speed drops again.
I have repeated this "cycle" several times: install express -> running slow -> removed express, installed developers -> running fast -> removed developers, installed express -> running slow -> removed express, installed developers -> running fast, etc. etc. etc.
Some SQL2005-related articles I read stated that Express edition equals in performance to other editions. This turns out to be untrue.
Anybody please suggest any explanation to this misfortune.
Thank you.
Eugene
View 7 Replies
View Related
Aug 18, 2006
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
View 1 Replies
View Related
Mar 22, 2007
I am not sure if this is the correct forum but here goes!
We rebuilt our SqlServer 2000 Trans replication the other night. It used to run in 3 hours but now it runs in 9.5 hours (7 hours bcp out, 2.5 hours bcp in). We have a dedicated distributor box (4 processors), a 4 processor publisher, and a 2 processor subscriber. None of the systems exhibited any processor stress or unusual disk activity. The network tests OK (tested with file xfers). But the bcp's wrote data at 2.5 to 4 minutes per 100k rows, and they loaded the data at about 100k rows in 10 seconds or less.
As you know, Replication Snapshot uses bcp on each source table to build a collection of flat-files. Then it uses bcp to load those files into the subscribing tables. Because bcp is the workhhorse here, I decided against posting this in the Replication forum.
The only change I know of is increasing MaxBcpThreads from 4 to 8. This parameter specifies the number of bulk-copy operations that can be performed in parallel. I was thinking that 8 bcp's might somehow be killing the drive where all the bcp files are written.
Any ideas?
View 2 Replies
View Related
Apr 12, 2007
I installed SP2 two days ago and it seems like my SSIS-packes now take longer time than before - the very opposite of what I was hoping for.
Anyway, here are some data from runs on our performance environment. No new data is added to the source database between the runs, but I do a full process of the cubes every time (time is in seconds):
Package...............................SP1...............................SP2
Load dimensions..................200.................................270
Load fact data.......................800...............................1600
Process cubes....................2100...............................2600
So, as you can see, everything is going slower with SP2. I have yet to look into if there are any specific steps in the packages that take longer time than before, but it's odd that all packages take a longer time to execute. Especially that cube processing is slower suprises me.
Has anyone experienced something similar? Thanks!
View 10 Replies
View Related
Nov 14, 2000
Hi gurus
I haven't put the code in since I've tried several variations & keep having the same problem: I'm hoping someone will recognise the problem from a description of it.
There are two parts to my query.
* Part 1 creates and then populates a temporary table
* Part 2 is a select query which joins the temporary table to a permanent table, on 2 fields including a datetime field. The data types on each side of the join are identical.
If I run the first part of the query through ISQL and wait for it to complete before running the second part (in the same ISQL window), it (the second part) takes just 3 minutes. However, if I run both parts together, the second part takes ages, in fact I'm not sure if it completes at all (could wait indefinately!).
I tried placing a 'GO' between the two queries when running them together, but it didn't seem to help.
Please help, I'm stumped.
Thanks
Jo
View 1 Replies
View Related
Oct 13, 2004
I have database, Visual basic as front end, and sql server as backend, the reports are using crystal report. Recently, the user complain it is too slow to run, it took a long to load the data, anybody help me? Thanks in advance.
View 3 Replies
View Related
Oct 29, 2005
I am new to SSIS and probably doing some mistake while transferring data from oracle source to oracle destination. Please guide me.. In our project we need to transfer nearly 80 GB data from old system (Oracle 9i) to new system (Oracle 10 G Release 2). To do this exercise we are testing and comparing performance and cost effectiveness between different tools like SSIS, DTS and SQL * Loader (oracle built in tool). We have selected one table, which is having 40 fields with 3 million records. The destination table is also having same structure. Surprisingly SSIS is giving slower performance than DTS!!!!! It is taking more than two hours or nearly two hours. I have tested the same process 3 times. I have used two servers (1 GB RAM, Dual processor) for source and destination with minimum load and used data flow task (OLEDB Source and OLEDB Destination). In case of transferring data from Oracle to SQL SERVER I am finding €śFast Loading Option€? in data access mode, which is giving considerable performance boosting. But while transferring data from Oracle to Oracle I am not finding €śFast Loading Option€? !!!!!!!!! For performance boosting which provider I should use?? Please suggest me€¦ if any one can€¦ would remain grateful to him€¦ Thanks and Regards Sudripta Rakshit.
View 18 Replies
View Related
Jan 14, 2008
I copied a code from one proc and created new proc with the same code but different name. Using the same parameter for both procedures I got the different time of execution. New one is 4 times slower. I went through execution plan and could not find any changes. Does anyone have any experiance like this?
View 2 Replies
View Related
Jul 19, 2007
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
View 8 Replies
View Related
May 19, 2008
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies
View Related
Feb 2, 2007
Hi,We have a C# web application that has been running for a few years now with little maintenance. Over the past few months, we have had some increasing problems.1. Loses session frequently ~ every 2-3 minutes.2. Occasionally (every 2 months) get out of memory exceptions.3. Connections to the DB and therefore the whole site run fast after restart. Gets continuously slower and slower over time until it starts throwing timeouts: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed
prior to completion of the operation or the server is not responding. The timeouts usually come every 3 weeks or so. Number 3 is the biggest problem for us, but I thought maybe 1 or 2 give some indication to why 3 is happening?I don't have much time to look into this now as I'm working on another project, but I was hoping that maybe someone would have ideas just looking at our symptoms.Thank you so much,Christie
View 4 Replies
View Related
May 3, 2001
I know this won't be a lot to go on but, we has a quad processor box that is doing a lot of sql crunching. When we turn off three of the processors it runs the SQL queries faster. The requests are comming from COM objects. CPU, Memory, page faults, all that stuff is fine. Also SQL dosen't appear to be using all the processors as only 1 has any amount of usage?. Any suggestions of where to start would be most appreaciated.
Mike
View 1 Replies
View Related
Jun 12, 2002
Ok,
here's a funky one That I can't find an expanation for. If I go into EM and choose a table from a database and return all rows, I get immediate results and can start browsing records. If I go into query analyzer and do a select * on the same table, it takes up to 20-25 minutes to return the result set. This used to only take like 5 mins. What gives? Anyone seen this before?
View 1 Replies
View Related
Aug 24, 2004
I have an issue with the BCP util, it is extremely slow when running a BCP in to the same database on an A/P cluster vs. Standalone system. It is a vendor application, so I have little control as to what I can do. The BCP runs from a jobserver (remote) through the network, but it runs the same way in the standalone system, yet the process is about 10X as slow with the cluster, and the only diff is cluster vs. standalone.
SQLClusterName is specified in -S, not the active node, and it uses a trusted connection, not a local account. Is there anything about a cluster config that would cause the BCP to run slower ?
Any thoughts ??
Thanks,
Paul
View 3 Replies
View Related
Jul 23, 2005
-- I have a situation where doing-- first example-- 1. Get series of values througha query into a string (@val)like'1,2,3,4':declare @val varchar(4000)select @Val = @val + cast(myval as varchar) + ',' -- myval is aninteger variablefrom xyzwhere xyz.field = 33SET @val = left(@val, len(@val) - 1)-- 2. EXEC a query using IN (' + @val + ')'EXEC('select *from qprwhere qpr.fieldx IN (' + @val + ')')-- is much faster than doing-- second exampleselect *from qprwhere qpr.fieldx IN (select myvalfrom xyzwhere xyz.field = 33)-- Since second example does not have a correlateed query, why is itslower?-- Thanks in advance,-- Caveman
View 6 Replies
View Related
Mar 24, 2007
Hi all,
In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.
Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?
Thanks,
Ning
View 2 Replies
View Related
Jan 4, 2007
I have a CF application I'm porting from VS2003/SQL CE 2.0 to VS2005/SQL Mobile. I make extensive use of RDA and unfortunately there is a huge performance degredation for me going to SQL Mobile. Where most PULL's in 2.0 take no more than a second or 2 to complete, they are taking around 10 or 11 seconds in SQL Mobile. This is the time from when .PULL starts and ends, it does not include any code run before or after the .PULL is issued, so it definitely something during the PULL that is slower.
I have tried settins of 0,1,3, and 6 for the compression level, all gave similar results, around 10 or 11 seconds. I have also tried doing a .SubmitSQL right before the .PULL to see if a sort of cached connection would help, but it only shaved a second off the time in most cases.
These times are using the same IIS server, SQL server (2000 SP3a), and database. I have tried using a 2003 SE device, WM 5.0 device, WM 5.0 emulator, and 2003 SE emulator, all exhibit the same performance degradation.
I really need to figure this out or I can't move the product to 2005 as the synchronization times would be completely unacceptable to our customers. Has anyone else run into this problem or know of a solution to it?
Thanks!
-Manny
View 1 Replies
View Related
Apr 19, 2006
I have a table valued UDF with only one SELECT query. It selects from a View, and has a couple of joins and calls another UDF in the WHERE clause. When I run the Select query itself it's duration is around 120, but when I have it wrapped in a UDF, it's around 1200!
ALTER FUNCTION [dbo].[fnGetiii]
(
bla bla bla
)
RETURNS @ItemTable TABLE
(
a varchar(2),
b bigint,
c smallmoney,
d varchar(50),
e varchar(50),
f int,
g varchar(50),
h datetime,
i int,
j int,
k int
)
AS
BEGIN
INSERT INTO @ItemTable
(a,b,c,d,e,f,g,h,i,j,k)
SELECT
vat.a AS a,
vat.b AS b,
vat.c AS c,
vat.d AS d,
vat.e AS e,
vat.f AS f,
vat.g AS g,
vat.h AS h,
vat.i AS i,
vat.j AS j,
vat.k AS k
FROM dbo.vw_jkl vat
LEFT JOIN dbo.uio uio
LEFT JOIN dbo.iop iop
ON (iop.kko = uio.kko)
ON (uio.jj = vat.jj)
WHERE this = that AND
this = that AND
this IN (SELECT iii FROM dbo.fniiireturn(pppp))
RETURN
END
How can I speed it up?
View 5 Replies
View Related
Aug 22, 2007
I was tasked with optimizing a query that was taking about 2.5 minutes to run. It is now running in 2 seconds, so great, it's working....except I don't understand why. The ONLY change made is the where...
Here is the origional
Code Snippet
WHERE (j.datelaunched > GETDATE() - 7 OR (j.jobtype_id = 2 AND j.datecreated > GETDATE() - 30)
Here is the optomized
Code Snippet
WHERE (j.datelaunched >= DATEADD(DD, DATEDIFF(DD,0,GETDATE() - 7), 0) OR (j.jobtype_id = 2 AND j.datecreated >= DATEADD(DD, DATEDIFF(DD,0,GETDATE() - 30), 0)))
View 10 Replies
View Related
Mar 13, 2006
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies
View Related
Apr 30, 2008
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
View 4 Replies
View Related
Dec 18, 2007
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
View 3 Replies
View Related
May 9, 2005
I have a table in which 2 of the fields are defined as TEXT. It was determined that neither of these fields would have to hold more than 3500 characters, so I changed both fields to VARCHAR (3500). The problem is that any query against this table takes 20 seconds to run. When the fields were defined as data type TEXT, queries ran lick-itty-split!? I would have expected just the opposite. Can someone explain this behavior?
View 1 Replies
View Related
Sep 3, 2004
I've got a table with a pk (bigint, no autoincrement) that has a clustered index. Same table has an integer field with a non-unique index on it.
When I do a count(*) on the table, the non-unique index is used (20m rows, 12 secs). When I force the count(*) to use the clustered index, it takes 43 secs. When selecting rows, usually the clustered index is used.
So I'm curious as to why the count(*) uses the non-unique index and the others don't. I've noticed it's faster but, why? Any ideas/considerations?
View 4 Replies
View Related
Feb 19, 2004
I have a large VB 6.0 application running with Jet 4.0 Access DB. I am considering moving the DB to MSDE. For testing, I installed MSDE on another computer in a peer to peer network running XP sp1, loaded up the DB from Access to MSDE2000A -- all without problem. (I should add the obvious fact I am new to using SQL server.)
What I find is that with a relatively small test DB, running just a single instance of the application, query response from MSDE is taking several seconds (4-5 second lag) longer than response from the Access DB, which runs extremely fast. This is with the Access DB installed on the same network drive, running the same application and the same queries syntax -- only changing configuration of connection for each (SQL vs Jet 4).
Any clues as to what may be going on? The lag time is unacceptable. I am using SQL password instead of NT security. It seems the process is perhaps lagging in the process of authorization.
Thanks for any suggestions or ideas on this.
View 1 Replies
View Related
