Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2005-07-15 : 08:58:11
|
| I have a to insert a lot of test-records in a database for testing the performance.I have some logic to fill the database with data that looks real (names are names, streets are readable etc.)I made a query in query analyzer. I have an INT @Records If I SET @Records = 1000it will make 1000 records (with 10.000 child records like phone #'s etc.)If I do a SELECT COUNT(*) FROM tblData in the beginning (in a new query window) the records are added fast. After a while it gets slower and slower.If I stop SQL Server and start it again and continu the query, it's fast again.How do I empty the cache or make the query fast again without having to stop and start the sql service (it's a test envirement).I tried things like:DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHEI might empty the cache in the code likeSET @Counter = @Counter +1IF @Counter > 5000BEGIN ClearBuffer or something SET @Counter = 0ENDMy server has only a 512 Mb of RAM, SQL 2000, Windows 2003 Server.Henri~~~~Anyone who can walk to the welfare office can walk to work. - Al Capone |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-15 : 09:43:00
|
| Odd that its slowing down. Are you adding the records with a random, or worse still reverse-order, primary key?Is there a primary key index, or maybe some other indexes? If that's likely to be an issue you could "import" the data in primary key order and create any other indexes after all the data is in so that they are optimally arranged, or use the DBCC DBREINDEX type commands to straighten-them-outKristen |
 |
|
|
|
|
|
Empty the cache or free up memory