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 |
|
sebasn
Starting Member
3 Posts |
 Posted - 2010-06-04 : 14:50:57
|
| Hi, im developing an app that should work in real time, so any delay noticed by the users its immediatly reported to us.The problem is that im using an in clause in my query wich covers lots of ids in my table (i need to get specifically the user's data), so the query goes like this:select field1, field2, ..., field12 from tablewhere date_field > (current_timestamp - '00:00:30') and table_id in (id1,id2,...,idN)I've a clustered index on table_id, fill factor 80%.I'm getting the info in about 0,5 seconds for 900 records (wich is the average ammount of ids to get), and sometimes more, wich is unacceptable. So testing a couple of things, i've figured i could try and join instead of using where table_id in...So i've created, for each session opened, a temp table called #ids and joining, the query looks like this...select field1, field2, ..., field12 from table inner join #ids on ID = table.table_idwhere date_field > (current_timestamp - '00:00:30')The response time seems to be better (< 0.3 seconds) but i'm a little concerned about having like 30+ temp tables since the server is writing down info all the time (and every time somebody restart the app, this temp table should be created once again) and honestly, this is my first time with #tables. So, should i be concerned about the overall performance in our server? Is it there a way to improve my querys that i'm not noticing? Btw, option (fast n) should not work, since i need the full recordset to process the data (i'm working on VB6)Thx in advance and sorry for my lousy english =P |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-04 : 15:04:27
|
| where do the ids come from, are they hard-coded. If you need just the specific user's data, wouldn't you wnat just his id?jimEveryday I learn something that somebody else already knew |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-08 : 05:57:11
|
| "....sorry for my lousy english" what's here is far better than some of us native speakers manage to write.PINning a table into memory may be of use, but this would only be advisable for a low volume and static table.Are you using Stored Procedures - benefit from cache? Are your tables qualified with "dbo."? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-08 : 07:38:39
|
Instead of providing the IN list within the query use a SPLIT function instead.select field1, field2, ..., field12 from table JOIN dbo.MySplitFunction('id1,id2,...,idN') ON MySplitColumnName = table_idwhere date_field > (current_timestamp - '00:00:30')In SQL2008 you could supply the "IN ID list" as a table - direct from the application, but as this is SQL2000 forum that's probably not relevant. |
|
|
|
sebasn
Starting Member
3 Posts |
Posted - 2010-06-08 : 08:46:11
|
quote:
Originally posted by jimf
where do the ids come from, are they hard-coded. If you need just the specific user's data, wouldn't you wnat just his id?jimEveryday I learn something that somebody else already knew
The ids are inside my app, since ive most of the user related info there. The thing is, that some users might (actually they do) share the same info. quote:
Originally posted by AndrewMurphy
"....sorry for my lousy english" what's here is far better than some of us native speakers manage to write.PINning a table into memory may be of use, but this would only be advisable for a low volume and static table.Are you using Stored Procedures - benefit from cache? Are your tables qualified with "dbo."?
I've tried using SPs, but run loads of test and didn't seem to have any effect on the querys performance. All tables are written as owner.table, so yeah, they are like dbo.table (inside the app and inside the SPs).quote:
Originally posted by Kristen
Instead of providing the IN list within the query use a SPLIT function instead.select field1, field2, ..., field12 from table JOIN dbo.MySplitFunction('id1,id2,...,idN') ON MySplitColumnName = table_idwhere date_field > (current_timestamp - '00:00:30')In SQL2008 you could supply the "IN ID list" as a table - direct from the application, but as this is SQL2000 forum that's probably not relevant.
Thx for the tip, i'll give it a try.Something i forgot to mention is that this server is over a 3MBPs VPN and the traffic seems to be over the edge, so after carefull research, i've figured that most of the delay seems to be from this low bandwith VPN (which sucks, if i may). So the query inside the server seems to be running fine (usually < 150 ms, i'm still working to reduce this time) but those crappy 3 MBPs are the real problem (and im afraid there aint much we can do about it).So, thx again for the tips and advices. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-08 : 10:17:46
|
| I think 900 IDs in an "IN" statement is a lot; beyond 100 and I would be avoiding using IN - although it may be better in SQL2008 than it was in SQl2000. (Anyway, you are still in SQl2000 land ...)One thing I have considered in the past with a client was to cache the IDs in SQL.Their app was:User does something, and a bunch of IDs are sent to client (along with data).User adds some "refine" parameters and resubmits which, in effect, sends the ID list back to the server - along with the "refine" parameters.By keeping the ID list at the server (indexed with a batch number or somesuch) they were able to just JOIN that to the other tables, instead of using "IN".Dunno if that would be an option for you though? |
|
|
|
|
|
|
|
|
Temporary Tables Performance