Hello,
We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.
We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.
We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.
Is there something we need to look into as far as read write speed/performance goes ?
TIA,
Avinash
Hi Avinash,
Could you provide a bit more information about how you determined the time it took to run the queries? Understanding your testing methodology will help determine if it is contributing or not.
Additionally, you mention you're using the auto attach feature, are you using User Instances as well? When using the VS UI to create connection to a database, the connection string specifies User Instance = True. This should be fine, but it's important to know.
Regards,
Mike Wachal
SQL Express team
-
Please mark your thread as Answered when you get your solution.
Hello Mike Wachal,
Yes we use User Instance and Auto Attach in the connection string to express.
At this time, I dont have a very 'scientific' or for that matter a very solid way to test out the performance. My question came from a general observation and thought I'd bounce it off the expert community to see if there was some caveats built into the use of express particularly with the auto attach mode.
Like I've already mentioned - our general observation is that queries 'Seem' to take longer on express both read and write when compared to 2005 server. Again, this may be a configuration thing aswell. But we are running the default configuration of express as done from within VS.Net 2005 setup and have made no changes what so ever.
On some counts we've put the start time and end time of execution in trace messages and have found the difference in execution times.
Thats all I have at this time,
Thanks and Regards,
Avinash
|||Thanks Avinash,
In general, SQL Express should perform similarly to other Editions of SQL Server, but we do have limitation that might affect performance. For one thing, SQL Express will only use a single CPU and will only address 1 GB of RAM. If your hardware has more than this, then you could see a performance difference because the non-Express edition would be able to use the extra hardware components to increass performance over Express Edition.
If you're hardware is the same and within the limitations of SQL Express, I'm not sure what could be the issue without knowing more about the specific queries and data that is being queried.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
Hello Mike,
Many thanks for your response. In our case both the server and the client run the exact same hardware. They are (both) running on 1GB RAM.
I'll watch out for any further issues I may run into - and then bring them to your notice with all the data that I can provide.
Thanks,
Avinash
|||OK Avinash,
Good luck with this.
As you find specific queries that perform differently you might want to bring those specific queries up in the SQL Database Engine forum. The folks in that forum will likely have some additional ideas both on tuning your queries and why they may perform differently on different Editions.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
No comments:
Post a Comment