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 |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-02-16 : 11:56:38
|
| My setup is an MS Access (97) application (which I can't change) connected to a SQL Server 7 via ODBC.We only have a small number of users (rarely more than 50 concurrent). None of the tables holds a large number of records, the largest is in the region of 100,000. All this means that although the application is very poorly written the system as a whole copes reasonably well from a users perspective.A few users have admin rights in the application to add data to lookups (used in the drop downs in the application). One of these in particular is having problems when searching which can take a minute or more to respond.What happens is that she goes to the part of the application she needs to and presses CTRL + F to bring up the standard search dialog on whichever field she wants to search in (often post code or address). This is what seems to take an inordinate amount of time. There are a number of issues here1) It seems to be worse for her than for some of the other users (though this could be down to impatience and perception2) I've run a trace when she was doing that and couldn't really work out what the problem was - there didn't appear to be any locks or deadlocks on the whole system. There is an index on post code (and a clustered index on the table but it doesn't include post code) but when she does a search for one it appears to do a table scan. When I put the query that it appeared to be running into QA it gave a reasonable execution plan and if she runs the same query twice, the second time it mostly seems to be quicker so it looks as though it is being cached (though not always). At the same time as this was happening we tried pinging the server and that was responding very quickly and anyway, no other users had complained of a slowdownOne thing I didn't really understand on the trace was that SET TEXTSIZE appeared a lot and it was setting to a very high value (I think about the highest value you can set). Is this something to do with the ODBC?Part of my problem is that I'm not really sure what I should be looking for in the trace details.I realise that this doesn't really give a lot to go on but if anyone has any suggestions or advice I would be grateful.Many thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
rishimainidba
Starting Member
30 Posts |
Posted - 2005-02-16 : 14:08:30
|
| As you told that for other users ur application is working fine and there is only this user who is facing some kind of delays so in this case you can certainly checkout ur network speed from this client to the Database Server. Just checkout whether ur Network card speed is configured for 100 Mbps or 10 Mbps. if ur Network card and ur Network can support upto 100 Mbps then checkout the Nework card speed and configure it for the maximum.Second thing that you can do is that try to ping to ur Server from the clients box with increased number of bytes instead of default 32 Bytes. You can do so by giving the following command:-ping servername -t -l 10000 ( This command will send packets of 10000 Bytes to the server). Just Checkout if in this case your Network does not loose any Network packets. if it does loose packets and u get request time out then u can contact ur Network administrator in this case.RegardsRishi |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-16 : 14:35:23
|
Isn't the network overhead going to be miniscule compared to the elapsed time? Assuming that the result of the query is only a couple of rows (right Elwoos??) the network traffic will be of the order of "peanuts" I reckon.I suggest you capture the actual SQL sent to the server (e.g. in Profiler), stick it in QA and run it in isolation.I expect the SQL from the application is dynamic (rather than, say, running an SProc), so the exact-same query will indeed be cached - whereas a search for a DIFFERENT PostCode will not - which would explain why re-running the query is faster.The querySELECT * FROM FOO WHERE BAR = 'XXX'will be put in the cache. However, running the query:SELECT * FROM FOO WHERE BAR = 'YYY'is, to you and me, an identical query plan, but to SQL it doesn;t match the cache.Now ... if you used a parameterised query with sp_ExecuteSQL then BOTH variants would reuse th same cache - but I doubt you application is doing that (maybe it could be made to?)Having got the SQL into QA use:SET STATISTICS IO ON; SET STATISTICS TIME ON... put query here - multiple sub-queries generated by Access if necessarySET STATISTICS IO OFF; SET STATISTICS TIME OFFand see how big the reported LOGICAL values are. Fiddle with indexes etc. to see if you can reduce those numbers. If you re-run the same query the LOGICAL values will not change (although the PHYSICAL might well), so working on getting the LOGICAL values as low as possible will be translated into better overall performance for the user.You'll be doing this on the brand spanking new SQL development server that your IT department gave you for Christmas, right?  Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-02-17 : 07:09:48
|
| Thanks GuysKristen I'll have a stab at what you suggested. I do understand about caching of queries. I'm quite rusty on using statistics and all that sort of stuff so what you have given me is pretty helpful (and will save me time looking it up)Some of your implications are correct in that the app will not be calling stored procedures (there aren't any other than the one's I use). It doesn't make use of many features of SQL server other than the ability to store information and to index it (unless there is something that I have added).I think that pretty much all the querying done by the application is dynamic as it will be generated within the application. What I wasn't so sure about was when this is something that is not really the application that was written by my supplier but a built in feature of MS ACCESS - I suppose that this is as dynamic as the SQL in the app itself as it will have to be. I am presuming that the FIND function in ACCESS generates some sort of dynamic SQL statement based on the user input.What is a bit odd is that this (apparently - haven't had time to check yet) happens to the same user regardless of the machine she is on. I sometimes wonder if it could be an ID ten T error?steveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-17 : 14:32:34
|
| You'll probably find that Access is converting dynamic SQL into sp_executeSQL parameterised calls, in order that they CAN be cached by the server. But if the queries are useless to begin with they will remain useless, of course!Kristen |
|
|
|
|
|
|
|
|
Tracing