URGENT SIMPLE HARDWARE REQUIREMENT PROBLEM

DEAR ALL THE MEMBER
I M WORKING ON A DATABASE PROJECT WHERE ON THE WEEKLY BASIC THE PROCESSING IS DONE ON THE LOCAL SYSTEM AFTER DOWNLOADING THE BACKUP DATABASE FROM ONLINE. ON THE WHOLE WEEK THE RECORD IS INSERTED ON A TEMP TABLE AND ON THE TIME OF PROCESSING IT INSERTED ON THE ACTUAL TABLE AND THAT TIME I USED TO DO SOME CALCULATION BUT ON TIME OF INSERT ON THE REAL TABLE IT USED TO CHECK ROW BY ROW OF THAT ACTUAL TABLE. AND WHEN NOW THE ACTUAL TABEL ROW IS MORE THAN 3000 I AM GETTING TOOO MUCH SLOW PERFORMANCE. IT TAKES AROUND 80 SECONDS TO INSERT ON ROW AND WEEKLY I HAVE ROUGHTLY 700 RECORD THAT MEAN IT TAKES ME 80*700/60/60=15 HOURS THEORY. BUT TAKE MORE TIME. I KNOW THIS IS A SIMPLE HARDWARE PROBLEM CAN ANY BODY HELP ME!!!!

THIS IS URGENT I HAVE ONLY FEW DAYS TO GIVE DECESION FOR NEW HARDWARE.

MY LOCAL SYSTEM IS
PIV 2.2GHz
512 MB RAM
80GB ATI HARDDISK
DATABASE SIZE IS 6MB
WINDOWS 2000 WITH SP4
MSSQL 2000 WITH SP3


WHAT I HAVE RECOMENED IS
XENON DUAL PROCESSOR,
2GB RAM


WHAT ONLY WANT TO KNOW THAT HOW MUCH TOTAL TIME IT TAKES TO FEATCH THE CURSOR HAVING AROUND 50000 RECORD ON THE XENON DAUL PRCOESSOR SYSTEM.



SHISIR [V]

Before you go upgrading hardware, you should determine if and where you have a hardware bottleneck. It is entirely possible your bottleneck is at the hard drive level (not enough spindles to handle the I/O). A good article on this site is athttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp.

You also may want to look into using bulk imports (although the number of inserts doesn't sound too high).

for such low performance, i would check 2 things,
1. the status of the OS disk cache
2. the status of the BIOS setting for the hard drive

1. try Computer Management, disk administration, right click the C: drive,
i don't use w2k anymore, so some one else needs to verify this
2. the BIOS disk property may not have 32-bit mode enabled

Thank u all for attention


on performance moniter
CPU = 100%
SQL Server Cache Hit Ration = 91.xxx%
and disk activity is almost none.


'=================================================================================================================
'=================================================================================================================
With rsCursor
.CursorLocation = adUseServer
.Open "SELECT transid FROM tblTrans", cnn, adOpenForwardOnly, adLockReadOnly
End With


rsCursor.MoveFirst

'--fetch first record
Do While rsCursor.EOF = False
' if i even remove my some calculation code it takes 44 seconds to loop to another record
' why it is taking such long time.
DoEvents
rsCursor.MoveNext
Loop

etime = Now()
'Debug.Print stime & " : " & etime
Debug.Print DateDiff("s", stime, etime)
'=================================================================================================================


the table tblTRans have around 3100 records. why it takes only 44 second for just a loop. it is fast till the around 1000 records. i think that i am less prcoessor power.


shisir

For a database of only 6 meg this does sound very odd to be having these performance problems.

Im sure new hardware would help, but to be honest Id be looking a little deeper before buying..
I know you seem pretty convinced the problem is hardware, but is that the root cause of the problem ?

Half a gig of RAM seems pretty adequate to me for a 6 meg database, but...

What other activity is occurring on the server?
What other apps are running on the server?
CPU is being pinned, but very little disk activity, and buffer cache ratio seem reasonable if a little low
Check to make sure you are not restricting the amount of memory being used by SQL.

Look in task manager, does sql server appear to be competing with other processes for cpu time, during the processing?

What is the execution plan of your insert?

Currently you select a large number of records for your cursor. Have you considered doing the cursor in smaller batches, perhaps remembering the last transid to be processed and looping in a while loop until there are no more remaining.

Finally I notice you are accessing this from client code. Can you reproduce the performance problems in query analyser using pure T-SQL? Just replicate the cursor code and see if the same performance problems exist in T-SQL, so you can eliminate any problems with your client libs etc

hello,

on performance monitor some time it goes to average upto 28 pages for more not most time it is 0 pages and Page Life Expectancy is grows constantaly. no other application is running. and CPU utlization is 99-100%


can anybody give me how much time takes on their system to loop 3000 record with only 2 integer field without any calculation on the field.


shisir

What is the network connection like between your client and server ? Or is this app running locally on the same machine as the sql server?

If the sql server is remote, have you tried using a client side cursor ? Ie, rsCursor.CursorLocation

Hi shishir,

the amount of time taken depends on lots of things... using something like QA to get 3000 rows on a client PC takes < 1 sec for me.

When you run SQLProfiler to capture any RPC:Completed and Batch:Completed with duration/cpu > 100ms, what do you see? As asked by Chappy can you confirm that there is definitely nothing else running on or against the server, and that you are saying that with a dedicated server for your purpose and that one statement you are having the problems that you describe...?

Cheers
Twan

no i have tested same local machine

as i have already said that only complete the whole loop for 3040 records takes more than 50 seconds on my machine. which is extermaly slow for me now. coz every time when record insersted on tblTrans it should loop throught the whole table (tblTrans) for once. so on every time the record increases the performance decreased dramttically. on the time of when i have only 1000 records it was fast only takes 4-8 seconds but when i have now only 3040 records it takes 50 seconds (for loop only). and the current db sise is around 7mb.


i have the problem is to loop throught the records set on the increasing records is very slow. since i have only PIV 2.2GHz
512 MB RAM
80GB ATI HARDDISK
DATABASE SIZE IS 6MB
WINDOWS 2000 WITH SP4
MSSQL 2000 WITH SP3

what i need to increase the faster loop on recordset. i have previously the coded on T-SQL for that procedure but now converted on ADO.

shisir

If your loop is based on a cursor in T-SQL i'm not suprised it is getting this slow. My recommendation would be to keep as far from cursors as possible since they are notoriousy slow.

Jaap Kramer

HI ya,

can you please confirm that there is absolutely nothing else going on on the server. i.e. you are the only user and the cpu usage is all used by the SQLServer.exe process The response times that you're getting seem bizarrely slow...

also use SQLProfiler to capture all RPC:Complete and TSQL:BatchComplete with duration > 100ms, and then let us take a look at this trace

Cheers
Twan

Do you have index on the table?
Did you considering the loop to a while loop in case you are doing with Cursor?