Hi
I want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.
Inside the loop i am opening the connection and closing after use. The sample code is below
for(int i=0;i<1000;i++)
{
sqlCmd.CommandText = "ProcName";
sqlCmd.Connection = sqlCon;
sqlCmd.Connection.Open():
sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?
Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?
Please clarify me these question.. Thanks in advance.
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
We have the same application installed on a few different environments with similar servers and similar hardward. The only difference is the versions of SQL and the colations. Is SQL 2005 a lot faster that SQL 2000? Could colation type make a big effect on performance? ScAndal
this line 'select * from [viewUserLatestFee]' executes instantly (in Query Analiser) this line 'select * from [viewUserLatestFee] where orgID = 1' takes up to 30 seconds for 1000 rows (still in Query analiser)
can anyone please help - I seem to have ran out of ideas
I have a feeling people might be curious about the view so here it is:
We used a stored proc to pull totals from a database. Everything was fine until the table grew and started to time out. So we created a temp table to populate with a range of data and then pull the totals from there. Everything was fine until the table grew and started to time out. Any suggestion?
I am newly joined as SQL DBA. I want to check the Physical disk Performance. we have RAID 5 with 5+1 disks. I calculated NO Of IO's Per Disk. But how do we know what is actual limit of IO's per disk.
What's my best bet in getting better performance out of one of my database servers? Currently we have 1 set of Raid5 disks partitioned into 2 drives. This houses everything (system, database, and logs) If that server has 2 slots left for drives I was thinking of putting 2 mirrored drives and getting the logs off the main database space? (Make sense?) This is a vendored application so working with new indexes etc. isn't something I should do wo/ the vendor's interaction. Will what I describe above help?
We have SQL Server running on a dual processor Pentium 500mhz server. Our database is hit by about 300 users. 200 of those users are doing constant searches though a client table of about 250,000 records, which in turn is linked to a history table containing over 5,000,000 records. This is only the tip of the iceberg, we have many triggers, procedures, updates, etc. going in the background. The database has over 500 tables.
Keep in mind, these searches that are taking place can involve all kinds of fields: phone number, company name, fax number, first name, last name, status, wildcard searches, etc. So as you can imagine, the database is being hit with all kinds of funky requests to find records. I will be the first to admit that our developers (vendor) are not the best code writers, and we have a tough time getting them to optimize something they do not even understand themselves.
As I speak, our processor utilization is maxing out between 95 to 100 percent. I've done a lot of performance tuning and all of the problems lie in the searching. We've built, tested, rebuilt, re-tested each and every index. I even used the Profiler to filter what I could. It has improved, but our database is growing at a rate of 10 megs a day (already close to 3 gigs, not that huge). I think I've optimized my indexes as best as I can considering all the fields and possibilities available to users to search for records.
For a database that requires all of these different search criteria, what would be a more optimal server? We are looking to purchase something ASAP. I could really use help from someone in a similar situation. It seems odd, in mind, that a company of 300 people would need to rely on a quad server (four processor capability.).
HI I have 700 to 900 mb of production database , 2 gb of ram , 30 gb hard disk, My production machine is runnng very slow , i have check everything memory, page/sec, catch hit ratin , dbcc dbreindex but still it performance is not up to the mark. If i stop SQL SERVER & restart for few days machine works fine but after that again same thing it work very slow, what could be the reason if any one had any solution please suggest. Thanks Nil
Hi friends, My company has aution web site, it is written in Java and all sql statements generated dynamically. No stored procedures used. If 30 users uses this site it is OK but if around 300 users uses then the site becomes very slow(almost dead) and developers saying that database is the bottle neck. Please help me in this problem how can I check and overcome this problem.
We have recently upgraded to SQL 7.0 on NT 4.0/sp6 box which has got 4 PIII 700 processors, 1GB RAM, and 70GB HDD on RAID 1 and RAID 5. We feel that the application performance is not great as expected in SS7. (The application was running in 6.5 smoothly and performance was good)
Is there any option needs to set to improve performance? Now, SS 7 using all the 4 processors and dynamically allocated memory, etc. Any thoughts greatly appreciated.
I'm running MS SQL Server on a 1.4 GHz AMD Athlon Processor with 750 MB or RAM and ample disk space. I have a table with 14 columns; 2 datetime, 8 int and the rest are varchar of various sizes less than 13.
I run a java process on another machine that connects to the database and insert records. It takes about 6 minutes to insert 100,000 records.
I run the xp performance monitor and only about 25% of the SQL Server machine's cpu is being used. I run top on the Linux box running java and I see about the same results. Neither machine is kept busy processing. Why don't I get better performance? Could my local area network be that slow? How many inserts per minutes is good performance?
Does anyone know the performance differences between returning data from SQL Server as XML vs. as a record set? We are about to dive into the For XML world full force, but we wanted to make sure that we are not heading for a performance nightmare.
Thanks for any insight on this. I'll try to look for white papers and do some testing in the meantime.
Declare Cursor for table A WHILE @@FETCH_STATUS = 0 Get values from other function based on some business logic. INSERT Into another table B (or) UPDATE to another table B END
I have to insert/update values to table B, one by one row. So, it is taking more time. Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.
1. where do we see the buffer cache hit ratio. can we set the buffer catche hit ratio manually. 2.In query execution plan we execute the query for performance issue.which parameters we check to take an action?
I build a query in SQL-server 2000 but i'm not happy with the performance, it takes about 15 minutes to execute the query (4 min INSERT and 11 min UPDATE). The table tbl_total has 3 million records and an index on Contract and Item, the table contracts has 1 million records and a key on Contract and Item. How can I speed up this query, is it for example possible to put an index on @table (internal table)?
INSERT INTO @table SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date) FROM tbl_total TOT WHERE EXISTS (SELECT 'X' FROM contracts CONT WHERE TOT.Contract = CONT.Contract AND TOT.Item = CONT.Item) GROUP BY TOT.Contract, TOT.Item
UPDATE contracts SET contracts.Change_date = TT.Change_date FROM contracts INNER JOIN @table TT On contracts.Contract = TT.Contract AND contracts.Item = TT.Item
i need help in gaining the performance of this query SELECT tblSuperClientFile.ClientRefNo, tblReferral.RefID, tblRail.RailDescr, tblReferral.SuperClientVendorID, tblVendor.VendorName AS Client, tblReferral.AssignedVendorID, tblReferral.ReferralDate, tblSpikeDate.DateCompleted AS PlanRevCompleted, tblReferral.CloseDate, tblCloseReason.CloseReason, tblBankruptcyInfo.BK_Filing_State, tblBankruptcyInfo.BK_Case_Number
INTO #PlanRev
FROM FNFBSDataMart.dbo.tblSpikeDate tblSpikeDate WITH (NOLOCK) INNER JOIN #ActiveBK ON tblSpikeDate.MasterID = #ActiveBK.MasterID AND tblSpikeDate.FID = 3160 AND tblSpikeDate.DateCompleted <= GetDate()-5 INNER JOIN FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK) ON tblReferral.RefID = tblSpikeDate.RefID AND tblReferral.ReferralDate >= GetDate()-180 AND tblReferral.AssignedVendorID NOT IN (188,1721) INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK) ON tblReferral.RefID = tblBankruptcyInfo.RefID AND #ActiveBK.bk_Case_Number = tblBankruptcyInfo.bk_Case_Number INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK) ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867) INNER JOIN FNFBSDataMart.dbo.tblRail tblRail WITH (NOLOCK) ON tblReferral.RailID = tblRail.RailID INNER JOIN FNFBSDataMart.dbo.tblVendor tblVendor WITH (NOLOCK) ON tblReferral.SuperClientVendorID = tblVendor.VendorID INNER JOIN FNFBSDataMart.dbo.tlkpState tlkpState WITH (NOLOCK) ON tblSuperClientFile.StateID = tlkpState.StateID AND (tblSuperClientFile.SuperClientVendorID <> 1820 OR tlkpState.Abbrev NOT IN ('AZ','AK','CA','HI','ID','NV','OR','TX','UT','WA')) LEFT OUTER JOIN FNFBSDataMart.dbo.tblCloseReason tblCloseReason WITH (NOLOCK) ON tblReferral.CloseReaID = tblCloseReason.CloseReaID
can anyone have a look at it and give me a feed back asap
This:SELECT MAX(TheDate) FROM MyTableor this:SELECT TOP 1 TheDate FROM MyTable ORDER BY TheDate DESCAs a follow up question to save me having to post, if I want a differentfield from the result set of a MAX query, how do I do it? ie. I want the"Condition" field of the record with the most recent date. I have beendoing it like this:SELECT TOP 1 Condition FROM MyTable ORDER BY TheDate DESCbut if MAX(TheDate) is quicker, I would like to SELECT TOP 1 Condition ....where TheDate is the max date...... Hope this makes sense.....Basically, I'm going to be performing this query nested inside another queryand I want the maximum performance possible (indexes are a differentquestion), which means trying to avoid table scans....
Hi...I have a server that responds to web pagesand back end processing....im not sure the best place to start to increaseperformance.....im a programmer..not a super dba but im pretty good...i have two servers at the isp site....wasthinking of putting all the backen store procedures on one server and whenthere invoked to retrieve the record sets from server1 ....looking for some ideas...on how to make this server performanceincrease.....thanksMark
Hi,Can anyone advise me of a quick way to estimate the time taken by DTS toimport a table (24 columns x 700,000 rows) from JD Edwards (running onAS400) into SQL Server (new table and no manipulation involved)?Many thanks,Steve
I have a stored proc which is the basis for a report and it takes approx 2 minutes to run from mgt studio. When the proc runs from the report however, it takes approx 6 minutes. This behavior is consistent and happens in a similar fashion with many of the procs behind the reports. It does not appear to be sql server load dependent, it does this when there is no other sql server load. any ideas?
I'm trying to pull data to the device by means of RDA and I faced the following issue. When trying to run the same query twice I get completely different results.
The first one takes much more time to run. It takes about a minute while the second time the same query takes about a second!!! Furthermore, MS SQL Profiler says that the first query comes to MS SQL only about 30-40 seconds after it was posted by the device. (emulator, device connected via ActiveSync or WiFi - it doesn't matter).
Could you advise what's the problem and what's the point I missed?
I've got a conversion job for a legacy system i'm sure some of you have seen
_dbo.MEMO_ memoid int
_dbo.MEMOTEXT_ memotextid int memoid int SEQNUMBER varchar(4) MEMOTEXT varchar(80)
MEMOTEXT stores 80-character chunks of text which I will instead be dumping into a varchar(max) in the new database.
I need to assemble the chunks into a single row so i can insert.
I've written a CTE, but it runs for 5 hours before the tempdb chokes on its tranlog.
I've simplified the code here:
Code Block
WITH CTE ( ID, memotextlist, [length], seqnumber ) AS ( SELECT ID = f.ID , memotextlist = CAST( '' AS VARCHAR(max) ) , [length] = 0 , seqnumber = 0 FROM dbo.MEMO f
UNION ALL
SELECT ID = fm.ID , memotextlist = CAST( c.memotextlist + ' ' + ltrim(rtrim(fm.memotext)) AS VARCHAR(max) ) , [length] = c.[length] + 1 , seqnumber = convert(int, fm.seqnumber) FROM CTE c inner join dbo.MEMO f ON c.ID = f.ID inner join dbo.MEMOTEXT fm on f.ID = fm.ID WHERE fm.seqnumber > c.seqnumber ) --INSERT... SELECT distinct d.ID , d.memotextlist , [length] , SEQNUMBER FROM ( SELECT ID = convert(int, c.ID) , memotextlist = ltrim(c.memotextlist) , [length] = c.[length] , SEQNUMBER = c.[SEQNUMBER] , [rank] = RANK() OVER ( PARTITION BY c.ID ORDER BY c.[length] DESC ) FROM CTE c ) d WHERE d.rank = 1
1. Is there a way to do this outside of a CTE? 2. Better way to do this inside a CTE? 3. Doesn't seem like SQL likes anything after the CTE declaration and before the SELECT that references it, so it doesn't appear you can create an index on the CTE that might improve things. ideas on indexing?
I've read where CTE's apparently perform poorly if there is more than one record at the second level. This would be my case, some MEMO's have a dozen chunks of text.
Also, nothing is getting picked up in the sys.dm_db_missing_index_ views that I haven't created from the source tables.
