| Author |
 Topic  |
|
drenard
Starting Member
USA
7 Posts |
 Posted - 11/14/2007 : 09:02:44
|
G'Day
I have been playing with express version and a
very large table over 500,000 records. I noticed
that Express seems to respond very sluggish.
Can anyone advise on settings for using large
tables or is it better to use the full version
SQL ?
Any insight would be great..
Thanx |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/14/2007 : 12:56:06
|
500,000 rows in a table is considered to be small not large.
Please post your problematic queries and the DDL for the table.
Switching to the full version isn't going to speed things up. The problem is most likely due to the database design and/or query design.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/14/2007 : 13:19:44
|
Maybe fragmentation?
What do DBCC SHOWCONTIG report?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/14/2007 : 16:19:42
|
"express version"
Maybe its being hosted on a machine configured as a "workstation" rather than a machine configured as a dedicated server. |
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 11/14/2007 : 23:37:25
|
| How often do you rebuild index or update statistics? |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 11/15/2007 : 07:58:37
|
Could you send a typical statement and indices on the table
Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
|
|
|
|
drenard
Starting Member
USA
7 Posts |
Posted - 11/19/2007 : 09:29:31
|
G'Day
Perhaps I did not explain my question carefully
enough.
The table has currently 640,000+ records in it,
Its basic information - Name,address,city,state,zip & phone.
I built a very simple web based front end to search this
table using VS2005. That all works.
My just question is in regards to general speed.
When I do a search it seems sluggish, while its
a whole lot faster then the original flat file.
It seems I am missing something in the overall
settings. So what I am looking for, is if you had
500,000+ records and wanted to install it in Sql Express
what kind of settings would you use to get the best
performence.
Thanx |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/19/2007 : 11:32:32
|
Again, please show us your table structure, all indexes, and an example of a SQL statement that performs poorly. It is impossible to help you to determine what the bottleneck is without that info.
- Jeff
http://weblogs.sqlteam.com/JeffS
|
|
|
|
KenW
Constraint Violating Yak Guru
USA
391 Posts |
Posted - 11/20/2007 : 14:05:32
|
quote:
Originally posted by jsmith8858
Again, please show us your table structure, all indexes, and an example of a SQL statement that performs poorly. It is impossible to help you to determine what the bottleneck is without that info.
- Jeff
http://weblogs.sqlteam.com/JeffS
++Jeff.
It never ceases to amaze me when people post questions like this and expect useful answers. It's like the people who call up the car repair shop and say "My car won't start. How much will it cost to fix it?" How can that be answered without a lot more information, like what make and model and year of car, what exactly "won't start" means, etc.?
To the OP: Either provide enough information (especially after being specifically asked for it) to allow us to help you, or don't waste our time posting useless questions. |
|
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 11/20/2007 : 14:32:55
|
| I'm going to go out on a limb and say he has very inefficient t-sql written to do the search and no indexes (or no useful indexes) on his table(s). |
|
|
|
Zoroaster
Aged Yak Warrior
USA
702 Posts |
Posted - 11/20/2007 : 15:51:34
|
quote:
Originally posted by drenard
G'Day
Perhaps I did not explain my question carefully
enough.
The table has currently 640,000+ records in it,
Its basic information - Name,address,city,state,zip & phone.
I built a very simple web based front end to search this
table using VS2005. That all works.
My just question is in regards to general speed.
When I do a search it seems sluggish, while its
a whole lot faster then the original flat file.
It seems I am missing something in the overall
settings. So what I am looking for, is if you had
500,000+ records and wanted to install it in Sql Express
what kind of settings would you use to get the best
performence.
Thanx
Honestly, your best bet here is to just do some google searching on SQL Server performance tuning and query optimization. There is a ton of stuff to read on the topic and it should get you going in the right direction.
If you are really serious about this stuff I would recommend getting some books on SQL Server in general like the stuff by Ken Henderson or Kalen Delaney also make sure to utilize books online. In addition, there is also a FAQ section here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210
that will help to answer a bunch of your questions. After doing the above research the forums here will be a great tool for you to get help on specifics.
Future guru in the making. |
Edited by - Zoroaster on 11/20/2007 15:52:02 |
|
|
|
drenard
Starting Member
USA
7 Posts |
Posted - 12/11/2007 : 17:32:41
|
G'Day
After some lengthy reading I was able to find some of the mistakes
I made. For those few of you who made rude comments, kiss my ass..
I asked a very simple general question.
If you had 500,000 records what approach would you take?
I did not ask for complete answer or how to's, just looking
for some advice to get me in the right direction. I dont claim
to have all the answers but I should be able to ask a simple
question without the bullshit to follow.
Peace
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/11/2007 : 17:43:27
|
You could make a difference by post your findings here so that other people can learn.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/11/2007 : 17:51:33
|
quote:
Originally posted by drenard
G'Day
After some lengthy reading I was able to find some of the mistakes
I made. For those few of you who made rude comments, kiss my ass..
I asked a very simple general question.
If you had 500,000 records what approach would you take?
I did not ask for complete answer or how to's, just looking
for some advice to get me in the right direction. I dont claim
to have all the answers but I should be able to ask a simple
question without the bullshit to follow.
Peace
Several people asked you for specific information, and you never provided it, so what did you expect?
In response to your last question: If you had 500,000 records what approach would you take?
I would ask for more information. There is nothing inherently fast or slow about 500,000 rows. Everything depends on the structure of the table, the criteria that you will use to do your lookup, and the nature of your data. In other words, it depends on the information that people were asking for and that you did not provide.
CODO ERGO SUM |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 12/12/2007 : 03:33:46
|
quote:
Originally posted by drenard
For those few of you who made rude comments, kiss my ass..
I asked a very simple general question.
If you had 500,000 records what approach would you take?
Then you deserve a very simple general answer - get a few years development experience under your belt, read several SQL Server books, look for articles regarding table structures, index selection & use, efficient programming etc.. Then come back and tell us why you would have been best advised to answer the questions repeatedly asked of you rather than ignore them and claim ignorance on other peoples' parts. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 12/12/2007 : 13:03:00
|
We need a feature request to click the ignore button for people like that haha! Filters the suckers out
Weblog -- http://weblogs.sqlteam.com/jhermiz |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 12/12/2007 : 15:12:01
|
quote:
Originally posted by jsmith8858
drenard -- which comments were the "rude" ones? Just curious.
- Jeff
http://weblogs.sqlteam.com/JeffS
He didn't answer any questions related to us trying to help him. (we needed the answers from him so that we could help him) What makes you think he's going to answer your question about which comments he considers rude?
As someone else put it in another thread, this guy is the kind of guy that calls the mechanic and says his car isn't running very well. When the mechanic asks him what it's doing, he just hangs up and then calls back to complain that the mechanic didn't help or was rude about it. You gotta give the mechanic a better description or take you car in in order to even attempt to get an answer.
I think the OP needs to take his midol and drink a nice glass of STFU.
|
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 12/12/2007 : 15:36:31
|
quote:
Originally posted by Van
I think the OP needs to take his midol and drink a nice glass of STFU.
looks like you just gave OP a post to point to if he wants to answer Jeff's question...
elsasoft.org |
|
|
|
Topic |
|
500,000+ records
