March 10, 2008 at 4:12 pm
Hello,
I am running simple queries against test SQL Express installation and they take a very long time to return data. I have two SQL Express instances installed on colleagues' machines to which I connect for my testing and both exhibit the same problem. The setup is Windows XP SP 2 with 2 GB RAM and 3.6 GHz CPU. I am querying a table with around 7000 records and my query is simply SELECT TOP 1000 * FROM MyTable. It takes over 10 seconds to return the recordset!
I have done the research and found posts, which talk about AUTO_CLOSE option, indexes, query execution plan, etc. I have done everything those posts recommend, but performance is still terrible. All the instances have SQL 2005 SP2 applied.
I also found that the query runs fast locally on each SQL Express instance, the problem seem to happen when I am trying to pull the data over the network.
I am really not sure what else to look for.
Thank you,
Michael
March 10, 2008 at 5:35 pm
How much data is it and how is it being "pulled over the network"?
Also, what is your network config?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 8:07 pm
Poor wireless signal will do this to you.
The 1000 rows - do they have text, image, ntext, varchar(max), varbinary(max), etc fields?
Try connecting to the other server using
np:[SERVERNAME]
or
tcp:[SERVERNAME]
where [SERVERNAME] is the name of the other computer possibly with an instance name such as OTHERCOMPUTER\MYINSTANCE.
You may find that "named pipes" performs better for you than a "tcp" connection or vice-versa. If you are using tcp and windows authentication then sometimes it can get slowed down whilst it looks for a domain controller, etc.
Could you mentally plot a graph of the following..?
1. Selecting 1 row
2. Selecting 10 rows
3. Selecting 50 rows
4. Selecting 100 rows
5. Selecting 200 rows
6. Selecting 500 rows
7. Selecting 1000 rows
If the problem is purely the amount of data then the time taken should be roughly linear. However, if #7 doesn't take much longer than #1 then you have some overall overhead with your network.
March 10, 2008 at 10:50 pm
Thank you for your reply. We are talking maybe a few MB of data (1000 records or medium size). Pulling over the network means that my coworker has SQL Express on his PC and I am querying it while sitting in the next cubicle (very technical, I know). Networkwise I checked connectivity with ping and tracert commands and all the responses are under 1 ms, so there are no networking issues between our computers as far as I can tell. I am also experiencing the same problem while trying to query his SQL Express from various other computers and servers I tried (thinking that maybe the issue was my particular installation). I have the same problem no matter from where I run my query. To rule out his particular computer as a culprit, I also installed SQL Express on on other PC and again I have the same issues. It's like there is something fundamentally wrong with SQL Express, but I can't pinpoint what exactly.
- Michael
March 10, 2008 at 11:14 pm
Hi
Are you having the same problem if you are querying a minimum no: of rows say around 50.
did u change the protocols and check.
Is there any Firewall .
"Keep Trying"
March 25, 2008 at 12:11 pm
Hi
Try this on database you are running queries.
alter database set AUTO_CLOSE OFF
March 25, 2008 at 2:01 pm
Thank you to all for your comments!
Yes, the AUTO_CLOSE is turned off. While do still have this issue, I think the problem is with the network connections between our computers. I ran Perfom while pulling data and the Bytes Send/Received per second counter is showing 100KB per second. This is only double the 50K modem speed! I still don't know why this is the case, but SQL Express is not to blame.
- Michael
June 3, 2008 at 1:35 am
I am facing the same problem, but i dont access db over network I have an application which connects to SQL Express 2005. If anybody has faced this issue before please help.
June 4, 2008 at 4:24 am
hi,
the problem is that you have to reindex you indexes
if you have a standard version then use the maintancce tool to rebuild the index
other wise you have to run a script to build it
you can look at http://www.sqldbatips.com/showarticle.asp?ID=27 to download a script that do it
HTH
Yaon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply