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 |
|
JonnyG
Starting Member
26 Posts |
 Posted - 2006-03-18 : 10:47:16
|
| I have a small database with 25,000 records.I have many fields, and some are memo fields (ntext).When I seach the text of the memo fields everything works well and is extremely fast.My question is: If I had 6 million records in my database, would the search of the memo fields still be just as fast?I currently query the database with ADO from within my delphi app, example sql.text:=('select * from table1 where memo1 like '+quotedstr('%filmbase%')+' and memo1 like '+quotedstr('%polyester%'));As I say, this works extremely well, I'm just wondering what the performance would be like on a database of 6 million records.thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-18 : 12:06:31
|
worse since there's so many rows.Go with the flow & have fun! Else fight the flow  |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-18 : 13:18:35
|
Without any other criteria, I guess the performance would be linear for this kind of search.I guess it would take appx 200 times longer  .Perhaps now the 25.000 rows are in memory as well, which might not be the case with 6 million.Do:DBCC DROPCLEANBUFFERSRun the query, multiply execution time by 200.(probably worst case scenario)By the way,passing dynamic sql like sql.text:=('select * from table1 where memo1 like '+quotedstr('%filmbase%')+' and memo1 like '+quotedstr('%polyester%'))is bad practice. (security,maintenance,performance tuning are example things that suffer)Create a procedure with input parameters and execute that from your code.rockmoose |
|
|
|
JonnyG
Starting Member
26 Posts |
Posted - 2006-03-18 : 13:21:29
|
| If it took 200 times longer then it would actually still be very fast as with 25,000 records it is (from the users perspective) "instant" |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-18 : 13:28:33
|
| Define "instant" and multiply by 200.the data is most likely cached in memory as well.rockmoose |
|
|
|
|
|
|
|
|
Searching memo or ntext fields