Query Too Slow! Need Some Performance Enhancing Tips!
Jul 20, 2005
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.
Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.
SELECT vehicle.vehicle_id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_item_assn] on
vehicle.vehicle_id=[vehicle_subj_item_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_item_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field_id=@template_field_id) AND
([template_field].template_field_type_id=3) AND
([vehicle_subj_item_assn].subj_item_value_text=@value) AND
(vehicle.end_dtm IS NOT NULL)
Thanks
Gavin
View 3 Replies
ADVERTISEMENT
Mar 3, 2008
Dear all,
I have the following stored procedure that takes around 1:15 minutes to finish execution against SQL Server 2005.
The table RecordedCalls contains 9369907 Records, the other tables used in the join will not contain more than 15 Records for each one.
The table lookups contains like 200 Records so that€™s why I put the records I want from it in a temp table (#tempLookUps) before joining it with the table RecordedCalls.
I have clustered index (Primary key) on the column ID in table RecordedCalls and non-clustered indexes on the columns that are used in the Where statement and the group by field (CallType), I can€™t remove any join with other tables or any condition on the where statements hence it is very dynamic and concatenated from other strored procedure and i can't remove the DISTINCT Word.
I found that when using temp tables to put the results in then imply joining on then is more efficient than using Derived Tables.
Are there any ideas to enhance the performance for this Stored Procedure? Or the code below is the optimal code?
Here is the Stored procedure:
DECLARE @max int
SELECT @max = MAX(RecordedCalls.ID) FROM RecordedCalls
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM Servers INNER JOIN RecordedCalls ON Servers.Name = RecordedCalls.ServerName
LEFT OUTER JOIN Tags INNER JOIN RecordedCallsTags ON Tags.ID = RecordedCallsTags.TagID
ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= @max
AND (RecordedCalls.CallDate BETWEEN CAST ('01 Jan 1910 00:00:00:000' AS DATETIME ) AND CAST('01 Jan 2210 00:00:00:000' AS DATETIME ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.AgentID NOT IN('1000010000')
AND RecordedCalls.IsDeleted='FALSE'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
View 2 Replies
View Related
May 9, 2008
Hi.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
Thanks,
Prasad
View 1 Replies
View Related
Dec 16, 2001
Originally posted by Jeremy at 12/10/2001 11:39:38 AM
Hello all,
I've written a simple dts job that uses oracle (8.x) as a source and oracle (8.x) as a destination. I'm using SQL 2000 and Microsoft's oledb provider for oracle as the two connections. I've chosen "Transform Data Task" with the following SQL "SELECT * FROM REPORTER_STATUS
WHERE LASTOCCURRENCE > TRUNC(SYSDATE)". As you can see, it's very simple, however it's very very very slow. (averages about 1000 rows per minute). In my column transformations, I've selected many to many versus the one to one. There are no activex scripts or anything along those lines. Just a simple push of the data from one oracle box to the other. The table schemas are identical etc... I've had this problem before with writing to Oracle and I can't imagine that it's really supposed to be this slow. If you need more details, please just let me know.
Thank you,
Jeremy
-----------------------------More -------------------------------
The official response from microsoft is that dts only allows for single inserts... not bulk or bcp for oracle. There must be someone out there who has figured out how to configure / modify / call (something) from a dts pacakage to insert millions of records into Oracle in a decent time frame...
thnx again..
View 2 Replies
View Related
Oct 22, 2004
When working with databases containing myriad of huge tables, I am very much tempted to create categorized views on those tables in order to simplify and facilitate data query programming? Some developers I talk to say such views generally slow down query performance. Is this true?
Thanks.
View 1 Replies
View Related
Aug 1, 2005
I have a table with about 20,000 records. So, to minimise performance issues, i try to only retrieve the top 100 records. The databaset retrieved can be paged and sorted. Below is my code, but it's taking quite a while even to only load the 100 records. Any suggestions?Private Sub BindGrid() Dim connectionString As String = "MyConnectionString" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT TOP 100 [Person].* FROM [Person] ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Try DataGrid1.DataSource = dataSet DataGrid1.Databind() Catch e As Exception DataGrid1.CurrentPageIndex = 0 End TryEnd Sub
View 1 Replies
View Related
Jul 23, 2005
Hi,Can't post specifics at the moment but if this seems like a commonproblem any help would be appreciated.When querying with ~6 tables, using mostly left outer joins, I getstandard performance with the where clausewhere XXX is not nullhowever, if I try the clausewhere XXX = 4the query takes upwards of 5-6 minutes (I just stop it running at thatpoint.The field XXX is in the 'main' table (to which the joins attach), it'san integer field too so I can't see too many problems there.There's no index on the XXX field, but if I remove the joins, I getstandard performance doing either query.So why would the second query's performance differ so significantly?Cheers,Chris
View 3 Replies
View Related
Jun 9, 2008
Hi everybody,
i would be thankful if anybody posts some references of the web sites
which relates to SQL Server Performance tuning.
Thanks in Advance
Suresh Kumar Goudampally
View 2 Replies
View Related
Jul 9, 2001
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
View 4 Replies
View Related
Jul 20, 2005
I am having performance issues on a SQL query in Access. My query isaccessing and joining several tables (one very large one). The tables arelinked ODBC. The client submits the query to the server, separated byseveral states. It appears the query is retrieving gigs of data from thetable and processing the joins on the client. Is there away to perform moreof the work on the server there by minimizing the amount of extraneous tabledata moving across the network and improving performance (woefully slowabout 6 hours)?
View 3 Replies
View Related
Jan 6, 2004
Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated)
-------------------------------------------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
-------------------------------------------------
end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Thanks for any time spent!
View 14 Replies
View Related
Aug 21, 2006
SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
View 6 Replies
View Related
Mar 24, 2007
Hi all...
I need urgent help, about someting:
i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
please help me what should i do......
View 3 Replies
View Related
Mar 30, 2000
I need to transfer a database from one server to another, I'm using the DTS utility because the servers have different sort orders. our database size is about 5GB which include about 2500 tables. Using DTS is taking many hours to transfer all objects and data. is there a better/faster way to do this?
Any help would be appreciated, Thank you
View 3 Replies
View Related
Mar 31, 1999
Help! I am new to SQL (6.5) and am designing a small database on a small LAN.
Everything seems to work fine until more than one user gets in the database.
It was originally an Access database and we upsized some of the information to SQL.
I have not upsized all the tables yet, and am not sure if that is causing a problem.
Since we are new to SQL we are trying to make all the changes in Access then upsize
everything. If this is the problem though, I will not be hesitant in upsizing.
Any ideas?
View 1 Replies
View Related
Dec 27, 2001
Since sp2 a number of applications, specifically web ado access has slowed very badly and I seem to be seeing a high cpu usage too. Internal jobs such as backups and table defrags are not affected. I'm on a 4 way box with ample hardware .. stats indexes etc. are fine .. client access is just slow. I suspect mdac but I can't pin anything down and it's driving me crazy !!!!!
I've even built a new box with a fresh install but to no avail.
help ???
View 3 Replies
View Related
Aug 24, 2004
I'm running the following statements. They have been running for 48 hours and counting... In performance monitor, I can see the system is steadily 100% disc bound. Any idea how to get status or ETA on these? Any idea how I can speed up such actions? This seems unusually slow. There are no errors in the Error Log. Does other DBMS systems (such as Oracle) handle such scenarios more quickly or with better status information?
INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)
UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)
Stages is 173 million records
Domains is 2.4 million records
This is running on pretty decent hardware:
Windows 2000 SP5
SQL Server 2000 Enterprise Edition SP3a
Dual 3.06 GHz Xeons with HyperThreading enabled (4 virtual CPUs)
4GB RAM (OS 3GB switch is enabled to give SQL Server 3GB of RAM)
70GB SCSI boot drive
Data/Transaction Log is on a RAID 5, 30 spindle, fibrechannel SAN
View 14 Replies
View Related
Aug 30, 2006
Hi - I have MS SBA2007beta and Office Outlook 2007 BCM Beta. Both use SQL2005 and they are both as slow to point of being frustrating. Outlook2007 without BCM is fine and every other application runs fine if no SQL server apps are present.
I have this running on a 2.8ghz desktop with 1G memory and a Laptop of 1.8ghz and 1.5g memory. The laptop is far faster.
When SQL apps are running everything is slowed.
View 1 Replies
View Related
Jan 31, 2000
Hello,
I have the same database on two different servers. One for production and one for testing. A view that I use runs in less than 2 seconds on the test system, but takes almost 2 minutes on the production server.
What I have noticed is on the test server the view will use an index. The production server ends up scanning a whole table. All indices are the same on both machines for the tables involved and I have updated the statistics. I even went through the process of creating a new table with its indices for the table that is being scanned. Both machines have had service pack 1 installed on them.
Any ideas?
View 1 Replies
View Related
Jan 28, 2003
An Application Times out with tons of SQL Server Locks but there is no log of any errors in SQL Servers. I checked all the Indexes and I reindex it but still same issues. CPU is 100% full at that time and users unable to do anything. Any ideas on how to fix it
View 1 Replies
View Related
Jun 5, 2004
Hi
We are facing performance related problem using Sql server 2000.
We have one stand alone P4 Pc (128 ram) and around 30 users access the sql server through network.
We have written our aplication in VB 6 and backend as Sql Server 2000. We have used Stored Procedure where ever necessary. We have used cursor location as Server side.
When we start with 5 users it is not slow, when all the users say 30 comes in it is slow down.
Can some one help to find out what is the problem.
Thanks
View 1 Replies
View Related
Nov 29, 2005
I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.
The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.
Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.
The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.
Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.
However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.
So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.
But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.
Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!
View 14 Replies
View Related
Feb 11, 2007
Hi,
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
Best Regards,
Manolis Perrakis
View 1 Replies
View Related
Jul 31, 2007
dear friends,
i have dropped many objects and recreated in a database.suddenly my database became very slow. so please any one of friends give solution.
View 9 Replies
View Related
Feb 7, 2008
I have a bunch of packages that take views and create tables from them. Some of the views are rather complex, but the packages themselves are very simple... drop and re-create a table using the data from a view on the same server. We create a new DB for each year, and this year we've upgraded to a new server with SQL 2005, so our DTS packages on the 2000 SQL server had to be recreated in SSIS on the new server. No problem, as I said the packages are really simple. But when I create the packages in SSIS they now take an extremely long time to execute, and I cannot figure out why.
For instance, one DTS package would take approximately 5 minutes to run when the view contained hundreds of thousands of rows and the underlying tables contained millions. But now, even with MUCH smaller tables (since it's the beginning of the year, new DB) the SSIS package I created on the new server takes over an hour, literally. The view that the SSIS package is using to create the table only takes about 15 seconds to execute in management studio (only about 16,000 rows). How can this possibly take so long??
the new server is virtually the same hardware-wise... 4 x 2400mhz, 4gb ram, win2k3 server
View 14 Replies
View Related
Jul 23, 2005
I have a stored procedure that has some problems with slow performance.The procedure has 2 parameters@firstnamevarchar(100)@lastnamevarchar(100)These parameters can have values like a%, that is wildcard searches.The strange thing is that if one of the parameters has the value %, andthe other one a%, the performance is very bad.If i subsistute the variables with exactly the same values hardcoded inthe where-clause, the query is very fast.If both variables has some characters prepending the percent sign, theperformance is better.SELECT distinct u.user_id, u.username, u.status_idFROM statusnames sn, statuses s, users u, users_persons up, persons p,users_roles urWHERE p.lastname like @lastnameAND p.firstname like @firstnameAND s.status_id = u.status_idAND sn.statusname_id = s.statusname_idAND u.user_id = up.user_idAND up.person_id = p.person_idWhat makes SQL server behave so differently with variables andhardcoded values?
View 6 Replies
View Related
Feb 19, 2008
Hi,We have shifted one of our data processing to a new instance of SQL2005. For this a new DB was created in SQL2005 and it is not anupgrade of SQL200 DB.The data processing application is a VB6 application that runs a batchprocess to insert about4.5 million records everyday in this SQL 2005 database. I am usingADO(ver 2.6) Connection object to execute T-SQL insert statements toinsert the 4.5 mill. records.This same application used to insert the data in the SQL 2000databaseand used to take about 2.5 hours to do so. After moving it to SQL2005 the performance has really detriorated and it now takes morethan6 hours.Is there any configuration change that I need to do in SQL 2005?Please help.Note: There is also an instance of SQL 2000 running in the sameserver.TIA,Tawfiq
View 3 Replies
View Related
Apr 12, 2007
I was transfering more that 100,000 records from flat file to sql table
It took about 1 hour.Is this the way it is?????i used oledb command.
As the data passes by i got to insert to several table.Like i insert some of incoming data to one table then get the key from that table and insert rest of the data with the key field from previous table to another table.
In this case i felt OLedb would be best as we can use query.
I cannot use oledb destination as it has only error output(to insert some of incoming data and i want to have a look up to get the key but oledb des has only error output)
i cannot use sql destination as the database is sql server 2000.It dosent let me.
How can i increase the performance????Please let me know
View 9 Replies
View Related
Dec 10, 2007
Hi.
I have successful upgraded from Windows 2000 / SQL Server 2000 to Windows 2003 / SQL Server 2005. Here is the process:
- Install Windows 2003 / SQL Server 2005 on new VM instance
- Detached SQL Server 2000 database -> Copy data to the new server and Attached to SQL Server 2005
- Run scripts to restore user login
- Change Compatibility level to: SQL Server 2005 (90)
Now, open 3rd party application and connect to the new database server (via IP address), the application was connecting ok and access data without no problem. However, database take more than 5 minute to connect/open instead of 30sec via the old the server?
Please adivse what could be the issue? Thanks!
View 7 Replies
View Related
Mar 7, 2003
DECLARE @num int
SET @num = 0
DECLARE @tableVariable table(ColA int, ColB decimal(18,4))
WHILE @num < 1000
BEGIN
INSERT INTO @tableVariable VALUES (2, 10.56)
SET @num = @num +1
ENDWhen this code is run in SQL Server 2000 Query Analyzer it commits in less than 1 second.
The same code run as a SQLServerAgent job takes 16 seconds.
Similar behaviour appears if INSERT statement is substituted with UPDATE.
SELECT statement runs equally nice on both alternatives.
Has anybody got an idea what might be the reason for slow execution of INSERT and UPDATE in a job?
Thanks,
teetjott
View 2 Replies
View Related
May 5, 2004
Will anyone be willing to direct me to a specific kb article for my problem? I've read the following articles: 251248,244661,259959, 280106 regarding troubleshooting a link to an Oracle server from SQL Server.
Our query performed very well (took 1 second) until the Oracle database was upgraded to 9i. To test we have links to both production and test on our test box. We are running SQL Server 2kSP3. Both Oracle databases are at 9i.
The query running against production (which is on a bigger, faster Unix box) runs in 5+ minutes whereas the same query run against their test, takes 1 second.
Using the Oracle client tool, SQLPLUS, the data comes back at lightening speed, but when the data goes through SQL Server, using the link to the Oracle production database, it has horrible performance.
Both database links are created the same. Both links display the table list of tables in Oracle in about the same amount of time when looking through EM. I've verified the presence of the mtxoci81_win2k.reg in the correct path.
The Oracle dba assures me that, other than a larger SGA and similar init parameters (which would be expected for a production environment), the other init parameters are the same. I've supported Oracle over the years (although I prefer SQL Server), and I respect this dba. He is thorough.
I've also changed the Server Options on the database link definition, checking them all and removing them 1 at a time and testing my query at each step. The last thing I'm looking at is the Provider Options, in case those settings are affected by the production Oracle 9i environment. I'm not coming up with much information regarding the Provider Options, hence my question to you.
The only other piece that I can see might be an issue would be that we have our sqlnet.ora file renamed. When present, our links don't work, when renamed, they do.
Any ideas or suggestions would be greatly appreciated!
TIA,
View 1 Replies
View Related
Jul 20, 2005
Hi to all,I've recently installed SQL Server 2000 on Dell Power Edge 1600sc withdual processor (2.8ghz) and 1 gig of ram, to use a CRM application(Maximizer Enterprise 8.0)and since we make the install every time auser is trying to access data it take about 5 to 10 second to see therequested data. The performance of the SQL server less than thepervise version of Maximizer.I've made a typical install of SQL server 2000 and SP3.Analysis Services as been installed too.I've seen a strange memory workload this week, the used memory wasabout 740,390 kb, from my knowledge of SQL, i think this is very notnormal.Any help will be welcomeThanks in advanceV. MesitiNetwork AdminRenmark Financial
View 1 Replies
View Related