Optimising Performance In SQL 6.5
Feb 8, 2001
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
View 2 Replies
ADVERTISEMENT
Feb 8, 2001
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
View 2 Replies
View Related
Jan 8, 2003
Hi,
I'm looking for tips, advice, best practice etc. on optimising a DB with over 300,000 user records to be accessed rapidly via a web interface. Any help would be greatly appreciated - specifically i'm looking at the different methods of DB optimisation indexing, clustering etc.
View 2 Replies
View Related
Apr 30, 2008
Hi all,
I have 20 SQL jobs thst are scheduled to run from say every 5mins to others that run every hour.
Does anyone know the best way to optimise these jobs to run.
At the moment once these jobs are running I cannot browse any tables in teh DB. I get a locked timeout rquest exceeded..
Do I need to stagger when the jobs run.
Or make one big job where they all run one after another ?
Any help ?
Ray..
View 3 Replies
View Related
Mar 26, 2008
Hi there
Recently our company purchased a product from ip2location.com; a database containing 2.9million IP address ranges, and their approximate cities/countries of registration.
Naturally, I thought - "Hey, wouldn't it be great if we could cross reference this with our IIS logs so we could see where our visitors are from?".
So, I set about doing just that. Our IIS logs are already in SQL.
The trouble is, the ip2location database is so large that executing a query against it to find which range a particular IP address is within takes me 1 second. Multiply that by 1,000,000 log rows, and Houston - we have a problem.
One of the issues is that each record in the ip2location database comprises a FROM_IP and TO_IP range to describe a range of IPs. So to find which IP range a particular IP resides in, I have to join using a BETWEEN statement (or so, I think anyway!).
Does anyone have any suggestions on how to improve this process, or has anyone done anything similar before?
Ideally, I'd like to write a trigger to grab the IP region data (i.e. City/Country) and update the IISLog with that value when the new row is inserted, saving me having to do it later.
I tried this, and the batch import of IIS logs into SQL took so long I got bored and gave up :)
Any help anyone can offer would be appreciated.
Many thanks
Richard.
P.S. Somebody is bound to ask - "Why couldn't you just use Google Analytics?"; my answer is because we want to slice up our log data into chunks, and give it to our customers in semi-real time. Plus the logs report on other services - not just HTTP. ;)
View 18 Replies
View Related
Jul 23, 2005
Hi,I have a problem I would really appreciate help with. I am generatingdynamic SQL and need to optimise it. The specific example I am trying tooptimise looks like this:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)AND (Data LIKE 'SIDE BY SIDE%' )) ))AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))I was OK optimising it with just 2 criteria and changed:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (( Data LIKE 'church%' ))))which took 26 seconds to using a derived tableSELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )CT2on T1.DataHeaderID = CT2.DataHeaderIDWHERE(T1.EntityFieldID IN ( 31) AND (T1.Data LIKE '12BORE%' ))and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.My problem is I need to write code to generate the SQL for 1 to n criteriaand am struggling to write the query for more than 2Best regards,Andrew
View 3 Replies
View Related
Mar 23, 2006
Hi.Maybe I'm just being dim, but I'm struggling to get my head aroundoptimising a query with regard to indexes. If I make a select query, suchas a pseudo-example 'select * from bigtable where foo='bar' and(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', howcould I make that any better? What indexes could I add, or what could Ichange in the query?I know it looks simple, but so am I.CheersChris Weston
View 5 Replies
View Related
Jul 28, 2006
Dear All, Plz help me in optimising the following query,Reduce repeatable reads from the table via select ,ythe table sare nothaving referntial integrity constarints ,relationsCREATE proc Rolex136SyncasDECLARE @date varchar(50),@ydate varchar(50)print CONVERT(char(11),(GETDATE()-1),100)SET @date =substring(CONVERT(char(11),(GETDATE()),100),5,2)+' -'+substring(CONVERT(char(11),(GETDATE()),100),1,3) +'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4) SET @ydate =substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)Print @datePrint @ydateinsert intobiiod.dbo.data_trans_currentday_test(MobileNo,UA,M essageID,ContentID,Description,MusicLabel,CPID,CPN ame,ContentType,Category,SubCategory,TransactionDa te,Units,Unitprice,Shortcode,Servicecode,OperatorI D,CatID,SubCatID,SpecialPackage,Royalties,Operator,Circle,OPGPName)(select mobileno,(SELECT CASE uawhen 'unknown' then nullelse uaend) as ua,(select case remarkswhen 'unknown' then nullelse remarksend) as remarks,contentid,(select case descriptionwhen 'unknown' then nullelse descriptionend) as description,(select musiclabel from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as musiclable,(select cpid from datalogs.dbo.contentprovider where cpname =datalogs.dbo.translogs.cpname) as cpid,cpname,contenttype,(select catname from datalogs.dbo.cont_Catg where catid in (selectcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as category,(select subcatname from datalogs.dbo.cont_subCatg where subcatid in(select subcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as subcategory,transactiondate,1 as Units, price,(select case servicenamewhen 'AIRTELIVE' then remarkswhen 'ALCOMBOPACKREG' then remarkswhen 'HINDI' then remarkswhen 'NOKIAGAL' then remarkswhen 'SUDOKU' then remarkswhen 'SUDOKU_APP' then remarkselse NULLend) as SHORTCODE,servicename,(select case servicenamewhen 'TSTTNEWS' THEN 600when 'TSTTWAP' THEN 600when 'TSTT_MMS' THEN 600when 'AKTEL' THEN 300when 'TELEMOVIL' THEN 700when 'COMCEL' THEN 701when 'QATAR2900' THEN 1ELSE(select operatorid from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as operatorid,(select catid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as catid,(select subcatid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as subcatid,(select specialpackage from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as specialpackage,(select Royalties from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as Royalties,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT'when 'ALCLICKWIN6464' then 'Airtel'when 'ALMMSPORTAL' then 'Airtel'when 'ALMMSSMSDWN' then 'Airtel'when 'ALMYALBUM646' then 'Airtel'when 'HINDU6397' thensubstring(remarks,1,PATINDEX('%.6397.%',remarks)-1)else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Operator,(select case servicenamewhen 'AKTEL' then 'Bangladesh'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'El Salvador'when 'COMCEL' THEN 'Gautemala'when 'TSTTNEWS' then 'Trinidad'when 'TSTTWAP' then 'Trinidad'when 'TSTT_MMS' then 'Trinidad'when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +6,len(remarks)-PATINDEX('%-%',remarks))else(select Circlename from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Circle,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT MMS'when 'ALCLICKWIN6464' then 'Airtel Click Win 646'when 'ALMMSPORTAL' then 'Airtel MMS'when 'ALMMSSMSDWN' then 'Airtel MMS SMS'when 'ALMYALBUM646' then 'Airtel My Album'when 'HINDU6397' then 'Hindu 6397'else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as OPGPNamefrom datalogs.dbo.translogs where transactiondate >= @ydate andtransactiondate < @date and servicename in('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))go
View 2 Replies
View Related
Dec 14, 2004
Hi all
I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.
Simple change to the stored procedure (this is just an example not the real stored proc):
From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’
Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.
Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:
strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.
So I created an index just for the strPostCode (non clustered).
This had no change to the ‘Like select’ what so ever. So I am now stuck.
1)Is there another way to search for part of a text field in SQL.
2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).
Any advice truly appreciated.
View 8 Replies
View Related
Jul 17, 2006
I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.
I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
View 5 Replies
View Related
Jan 4, 2007
I need to merge replicate data to two different types of subscribers:
Clients subscribers which will have a very small percentage of the data from the central database. The data on these machines will be managed using dynamic filtering on host_name()
Server subscribers which will manage a copy of all the data from the central database
There will be far fewer server subscribers than client subscribers.
As I see it I have two options for the configuration
1) Use two separate merge publications €“ one which is filtered and one which isn€™t
2) Use a single merge publication and setup the filtering so that the server subscribers receive all the rows
Which option is likely to lead to better performance?
With option 1) there would be 2 complete sets of replication metadata which need to be maintained €“ so I am tending towards option 2. Are there any disadvantages in using a dynamic filter to return a very large number of rows?
View 6 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jun 22, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies
View Related
Mar 9, 2007
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
View 1 Replies
View Related
Aug 31, 2007
HiI 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 belowfor(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.
View 1 Replies
View Related
Dec 8, 2003
I have a following problem with SQL performance:
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:
SELECT dbo.viewUserPosition.id, dbo.viewUserPosition.username, dbo.viewUserPosition.password, dbo.viewUserPosition.title,
dbo.viewUserPosition.firstName, dbo.viewUserPosition.lastName, dbo.viewUserPosition.email, dbo.viewUserPosition.address1,
dbo.viewUserPosition.address2, dbo.viewUserPosition.suburb, dbo.viewUserPosition.postcode, dbo.viewUserPosition.country,
dbo.viewUserPosition.state, dbo.viewUserPosition.mailAddress1, dbo.viewUserPosition.mailAddress2, dbo.viewUserPosition.mailSuburb,
dbo.viewUserPosition.mailPostcode, dbo.viewUserPosition.mailCountry, dbo.viewUserPosition.mailState, dbo.viewUserPosition.birthDate,
dbo.viewUserPosition.joinDate, dbo.viewUserPosition.lastUpdated, dbo.viewUserPosition.orgID, dbo.viewUserPosition.positionID,
dbo.viewLatestPaidFee.feeID, dbo.viewLatestPaidFee.mshipID, dbo.viewLatestPaidFee.name, dbo.viewLatestPaidFee.[desc],
dbo.viewLatestPaidFee.terms, dbo.viewLatestPaidFee.period, dbo.viewLatestPaidFee.periodType, dbo.viewLatestPaidFee.fee,
dbo.viewLatestPaidFee.startDate, dbo.viewLatestPaidFee.endDate, dbo.viewLatestPaidFee.deleted, dbo.viewLatestPaidFee.feePaidID,
dbo.viewLatestPaidFee.paidDate, dbo.viewLatestPaidFee.effectiveDate, dbo.viewLatestPaidFee.approved, dbo.viewLatestPaidFee.optionID,
dbo.viewLatestPaidFee.paidAmount, dbo.viewLatestPaidFee.feePaidEndDate
FROM dbo.viewUserPosition LEFT OUTER JOIN
dbo.viewLatestPaidFee ON dbo.viewUserPosition.id = dbo.viewLatestPaidFee.userID
Here is viewUserPosition:
SELECT dbo.tblUser.id, dbo.tblUser.username, dbo.tblUser.password, dbo.tblUser.title, dbo.tblUser.firstName, dbo.tblUser.lastName, dbo.tblUser.email,
dbo.tblUser.address1, dbo.tblUser.address2, dbo.tblUser.suburb, dbo.tblUser.postcode, dbo.tblUser.country, dbo.tblUser.state,
dbo.tblUser.mailAddress1, dbo.tblUser.mailAddress2, dbo.tblUser.mailSuburb, dbo.tblUser.mailPostcode, dbo.tblUser.mailCountry,
dbo.tblUser.mailState, dbo.tblUser.birthDate, dbo.tblUser.joinDate, dbo.tblUser.lastUpdated, dbo.tblRelPosition.orgID,
dbo.tblRelPosition.positionID
FROM dbo.tblUser INNER JOIN
dbo.tblRelPosition ON dbo.tblUser.id = dbo.tblRelPosition.userID
and viewLatestPaidFee:
SELECT dbo.tblMshipFee.id AS feeID, dbo.tblMshipFee.mshipID, dbo.tblMshipFee.name, dbo.tblMshipFee.[desc], dbo.tblMshipFee.terms,
dbo.tblMshipFee.period, dbo.tblMshipFee.periodType, dbo.tblMshipFee.fee, dbo.tblMshipFee.startDate, dbo.tblMshipFee.endDate,
dbo.tblMshipFee.deleted, fp.id AS feePaidID, fp.paidDate, fp.effectiveDate, fp.approved, fp.optionID, fp.paidAmount, fp.endDate AS feePaidEndDate,
fp.userID
FROM dbo.tblRelMshipFeePaid fp INNER JOIN
dbo.tblMshipFee ON dbo.tblMshipFee.id = fp.feeID AND fp.endDate =
(SELECT MAX(fp2.[endDate])
FROM [dbo].[tblRelMshipFeePaid] fp2
WHERE fp2.[userID] = fp.[userID])
View 4 Replies
View Related
Jan 13, 2005
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?
View 3 Replies
View Related
Jan 17, 2002
Hi,
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.
Thanks
Praveen
View 1 Replies
View Related
May 8, 2001
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?
Thanks
View 2 Replies
View Related
Mar 31, 2001
hi,
i am using to move data from oracle to oracle.
i have used stored procedure in oracle for the update/insert .
the dts calls the stored procedure for each record, due to this the performance has gone down. how do i increase the speed of data xfer.
has any one done any thing similar ?
Tushar
View 1 Replies
View Related
Jun 26, 2001
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.).
Thanks. JT
View 3 Replies
View Related
May 31, 2000
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
View 2 Replies
View Related
Jan 17, 2000
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.
Thanks
dindu
View 2 Replies
View Related
Apr 27, 2000
I am running a SQL 7.0 server on a two processor machine. We are having some performance issues.
one of the processor is always above 90% utilization but the second is barely at 50%.
Will adding another processor help or are the processes locked to one processor.
The server is a dedicated sql server. nothing else is running on it.
Thanks for any info you can provide.
Pierre
View 2 Replies
View Related
Oct 20, 1999
Hi,
What I have to do to determine which is the capacity (transactions / sec) of MS SQL Server 7.0 on a specific hardware configuration?
Thank you,
Sebastian Bologescu
View 1 Replies
View Related
May 5, 2001
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.
Thanks in Advance
Jaya
View 2 Replies
View Related
Mar 14, 2002
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?
Thanks for your input.
View 1 Replies
View Related
Jan 23, 2001
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.
View 3 Replies
View Related
Feb 5, 2004
I ave the following Code in my Stored procedure.
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.
View 11 Replies
View Related
Apr 4, 2008
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?
View 4 Replies
View Related
Apr 14, 2008
I have a small doubt. If we keep our data files and log files on sepertate disks how this can improve the database performance.
View 2 Replies
View Related
