SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 500,000+ records
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

drenard
Starting Member

USA
7 Posts

Posted - 11/14/2007 :  09:02:44  Show Profile  Visit drenard's Homepage  Reply with Quote
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
37133 Posts

Posted - 11/14/2007 :  12:56:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/14/2007 :  13:19:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe fragmentation?

What do DBCC SHOWCONTIG report?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/14/2007 :  16:19:42  Show Profile  Reply with Quote
"express version"

Maybe its being hosted on a machine configured as a "workstation" rather than a machine configured as a dedicated server.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/14/2007 :  23:37:25  Show Profile  Reply with Quote
How often do you rebuild index or update statistics?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 11/15/2007 :  07:58:37  Show Profile  Visit jackv's Homepage  Reply with Quote
Could you send a typical statement and indices on the table

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Go to Top of Page

drenard
Starting Member

USA
7 Posts

Posted - 11/19/2007 :  09:29:31  Show Profile  Visit drenard's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/19/2007 :  11:32:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 11/20/2007 :  14:05:32  Show Profile  Reply with Quote
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.
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 11/20/2007 :  14:32:55  Show Profile  Reply with Quote
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).
Go to Top of Page

Zoroaster
Aged Yak Warrior

USA
702 Posts

Posted - 11/20/2007 :  15:51:34  Show Profile  Reply with Quote
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
Go to Top of Page

drenard
Starting Member

USA
7 Posts

Posted - 12/11/2007 :  17:32:41  Show Profile  Visit drenard's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/11/2007 :  17:43:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/11/2007 :  17:51:33  Show Profile  Reply with Quote
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
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 12/12/2007 :  03:33:46  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/12/2007 :  03:57:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/12/2007 :  11:33:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
drenard -- which comments were the "rude" ones? Just curious.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/12/2007 :  13:03:00  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/12/2007 :  13:53:21  Show Profile  Reply with Quote
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




Edited by - X002548 on 12/12/2007 13:54:48
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 12/12/2007 :  15:12:01  Show Profile  Reply with Quote
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.

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/12/2007 :  15:36:31  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000