500,000+ records - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

500,000+ records

Author

Topic

drenard
Starting Member

7 Posts

Posted - 2007-11-14 : 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

38200 Posts

Posted - 2007-11-14 : 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

30421 Posts

Posted - 2007-11-14 : 13:19:44

Maybe fragmentation?

What do DBCC SHOWCONTIG report?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-14 : 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
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-14 : 23:37:25

How often do you rebuild index or update statistics?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-11-15 : 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

7 Posts

Posted - 2007-11-19 : 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

7423 Posts

Posted - 2007-11-19 : 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

391 Posts

Posted - 2007-11-20 : 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

462 Posts

Posted - 2007-11-20 : 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

702 Posts

Posted - 2007-11-20 : 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.

drenard
Starting Member

7 Posts

Posted - 2007-12-11 : 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

30421 Posts

Posted - 2007-12-11 : 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)

7020 Posts

Posted - 2007-12-11 : 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

1064 Posts

Posted - 2007-12-12 : 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

30421 Posts

Posted - 2007-12-12 : 03:57:35

He was asked for additional information long before this topic, but he didn't respond then either.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88331

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-12 : 11:33:26

drenard -- which comments were the "rude" ones? Just curious.

- Jeff
http://weblogs.sqlteam.com/JeffS

jhermiz

3564 Posts

Posted - 2007-12-12 : 13:03:00

We need a feature request to click the ignore button for people like that haha! Filters the suckers out

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

X002548
Not Just a Number

15586 Posts

Posted - 2007-12-12 : 13:53:21

quote:
Originally posted by drenard

If you had 500,000 records what approach would you take?

I would post they asked you to post

quote:

Peace

Seriously?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-12 : 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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-12 : 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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources