Fastest Computer 4 SQL Queries

Toggle sidebar Toggle sidebar
Y

ytoledano

Distinguished
Jan 16, 2003
974
0
18,980
Hi,
I need info what is the fastest computer I can get for querying a DB.
The computer will have a large DB of several GBs and will run in what I understand is an SQL Server configuration only the queries are run locally - the computer is not connected to a LAN for security purposes.
The processing is not paralleled so I'm looking at a single-processor, single-core system. Speed is most important so I'm willing to pay a pretty buck on the best CPU, but I still need to know which is better at this - Inter or AMD.

I see no reason to go with an Opteron/Xeon solution, but stay with the enthusiast's Athlon/Pentium, though I may be wrong.

How much does cache/freq./latency weigh-in? Will I benefit from 2 GBs of RAM?

Also, will I benefit from a fast HD and if so, is it worth paying for some 15K, 0+1/5 SCSI RAID?

<b>Behold, Mine anger and My fury shall be poured out upon this place upon man and upon beast and upon the trees of the field and upon the fruit of the land and it shall burn and shall not be quenched
 
What SQL app doesn't support multiple CPU's ?

Fastest from Pentium/Athlon is AMD FX-57, or Opteron 254 (Opteron 254 is cheaper).
But it depends what app is used, since in some cases P4 can be faster.
But good thumb rule is: AMD is faster with bigger Databases.

You will benefit from >2GB RAM if your application and OS supports it.

<font color=red>"We can't solve problems by using the same kind of thinking we used when we created them."
- Albert Einstein</font color=red><P ID="edit"><FONT SIZE=-1><EM>Edited by HansGruber on 08/26/05 01:56 AM.</EM></FONT></P>
 
>will have a large DB of several GBs

SQL Server will cache as much data in RAM as it can. If query speed is going to pay back the cost of this machine, your first investment is to max out the system RAM. Then of course you want to make sure SQL Server is configured properly to be able to use that RAM. For optimum results, it would be good to have enough RAM to hold the entire database in memory. If not, you'll probably want to consider a RAID 5 array, using the fastest drives your budget allows.

>The processing is not paralleled

You may be mistaken on that point. From the SQL Server 2000 Books Online: "Queries heavily consuming CPU cycles are the best candidates for a parallel query. For example, joins of large tables, substantial aggregations, and sorting of large result sets are good candidates". Of course SQL Server will only parallelize the execution plan if the machine has multiple processors.

But after all is said and done - how the queries are coded, and how the database is designed, can have as much - or more - effect as the speed of your hardware. I've seen queries go from hours to minutes with simple code tweaks and the addition of useful indexes.

And since this is supposed to be a CPU forum....dual core CPU's might perform better. :)
 
If the lack of redundancy is acceptable, a raid 0 array will perform better since queries on large db's will generate a good amount of writes for scratch files and Raid 5 isn't best for writes.

Mike.

<font color=blue>Outside of a dog, a book is man's best friend. Inside the dog its too dark to read.
-- Groucho Marx</font color=blue>
 
I checked with the programmer and she said that queries are not paralleled. Maybe the queries can be tweaked but as I understand they're extremely complex mathematically, and considering it took months to write them, I don't know if it's worth hiring a guy to rewrite them.

As it turns out I'm also limited to $1K-1.3K for the whole rig. So now I'm stumped whether I should invest in 2 GBs of RAM, second HD (for RAID 0, as redundancy is not necessary (redundant, you might say)), or a second core, in case there is some software parallelization.

I think I'll stick to an Athlon since the Opteron requires ECC/RE RAM (right?).

What about the Athlon not being able to use 4 modules at DDR400, has that been fixed?

<b>Behold, Mine anger and My fury shall be poured out upon this place upon man and upon beast and upon the trees of the field and upon the fruit of the land and it shall burn and shall not be quenched<P ID="edit"><FONT SIZE=-1><EM>Edited by ytoledano on 08/27/05 03:43 PM.</EM></FONT></P>
 
It can use 4 sticks of RAM, but at 2T command rate (0-5% loss in performance).

I would invest to memory, some 4GB with 64bit OS.

But it depends how random those queries are ?
If queries go thru DB in certain order, you don't need fastest HDD's (RAM is filled as you progress).
But if queries are random, there is going to be lower cache hit rate, so highspeed HDD would help a lot.

ps. that 64bit OS is a must, it's much faster with SQL queries (if your app supports it).

<font color=red>"We can't solve problems by using the same kind of thinking we used when we created them."
- Albert Einstein</font color=red><P ID="edit"><FONT SIZE=-1><EM>Edited by HansGruber on 08/28/05 00:48 AM.</EM></FONT></P>
 
For now I'll go with the 3800+ Venice and 2*1 GB 2.5-3-3-7 Roswill RAM. These 2 I'm ordering from Newegg.

The queries go over data gathered over time but don't write to any tables, at least nothing more than a couple hundred cells. About storage, will NCQ improve performance, and is it supported on most 939 boards?

I also need advice on a decent 939 board with SATA RAID (I've seen most have) and having VGA out is a plus. I also don't wanna go too much over budget and I still haven't picked an HD.

<b>Behold, Mine anger and My fury shall be poured out upon this place upon man and upon beast and upon the trees of the field and upon the fruit of the land and it shall burn and shall not be quenched
 
I would get 1MB L2 instead of 512kB that's in venice core.
It should help quite a lot with SQL.

<font color=red>"We can't solve problems by using the same kind of thinking we used when we created them."
- Albert Einstein</font color=red>
 
From someone who has one of the fastest SQL servers on the net. If you want to do it right the first time:

Intel E7520 chipset
Dual Nacona's
Raid 5 with 5x SCSI 320 15K RPM
4GB ~ 16GB onboard, use the memory mirror option for higher level of reliability.


<A HREF="http://www.xtremesystems.org" target="_new">www.xtremesystems.org</A>
 
Judging from all these reviews, I can't see you having the fastest server with Xeons on it:

http://www.tomshardware.com/cpu/20030422/opteron-17.html
http://www.anandtech.com/IT/showdoc.aspx?i=1982&p=8
http://www.anandtech.com/linux/showdoc.aspx?i=2163
http://www.eweek.com/article2/0,1759,1403690,00.asp
http://www.tecchannel.de/server/hardware/402167/
http://www.open-mag.com/9086339824.shtml
http://www.sudhian.com/showdocs.cfm?aid=487
http://lists.debian.org/debian-amd64/2004/07/msg00286.html

The good thing about Opteron servers is that they scale a lot better whit more processors on it. 😉

I can post more and more reviews, but I ain't doing something like that (google is your friend 😀 )

My Beloved Rig:

ATHLON 64 FX 55
2X1024 CORSAIR XMX XPERT MODULES
MSI K8N DIAMOND (SLI)
2 MSI 6800 ULTRA (SLI MODE)
OCZ POWERSTREAM 600W PSU<P ID="edit"><FONT SIZE=-1><EM>Edited by Bullshitter on 08/29/05 07:01 PM.</EM></FONT></P>
 
I checked with the programmer and she said that queries are not paralleled. Maybe the queries can be tweaked but as I understand they're extremely complex mathematically, and considering it took months to write them, I don't know if it's worth hiring a guy to rewrite them.
Click to expand...
The actual QUERY is not paralleled, but the SQL could analyse the query and separate it in multiple individual task.

For example, if a query request to merge data from 2 table. The SQL server can open both table simultaneously by using sending the reading command to 2 different CPU/thread.

I'm 99% sure that a DUAL-CORE system will give you a good boost in performance for the same price (compared to single core system).

NOTE : I know SQL because I program in MySQL/PHP and I also access ORACLE databases with my PHP scripts once in a while.

-
GA-K8NF-9 / <b><font color=green>Athlon 64 3200+</font color=green> @ 3800+</b>
Infineon DDR400 (CL2.5) 2x512Megs
<font color=green>GeForce 6600GT 128Megs</font color=green>
<A HREF="http://www.getfirefox.com" target="_new">Get Firefox!</A>
 
the road, i just read your last statement



is there a way to contact you for support for PHP / MySQL coding help?

I'm relatively new at doing it. but I've already got an application in place for work based upon it. but the second part of it has me stumped. maybe a professional could give me an idea.

so far I've written a program that does this with an SQL database and PHP / web front end. we're a recruiting / temp agency.

it is a contractor / workee placement and trackgin system that keeps a complete history of who / where / when / we placed all workers with given payrates and pertinent information.basically all the end user has to do is click a button on workers profile enter in a pay code and all the necessary documentation is created for faxing and submittal to corporate. it also saves the information and allows for retreival anytime. i'm proud of it. but i've ran into a few little problems.

a feature i added that allows us to pull up the actual paid hours per week always times out. that particular table alone is over 1gig. since we employ approximately 5,000 / year and have history in there for over 5 years. the data is quite large.

I've tried several basic SQL queries, but they always time out because it'sjsut taking too long to find the information. i know it DOES work because it will return the start of all the pay info, but then times out and just takes a LONG time.

I also know it's not the hardware since it's a real server rig with 2 x XEON's w/ hyperthread @ 2.6ghz. 4gb RAM, and the SCSI ahrd drives in RAID 5.
 
I'm not an EXPERT in database. It's more a hobby, but I read a lot and help friends/co-worker build simple DB.

I would recommend you to split your table. You could easily keep in your main table only the data related to the last months (based on your need). So, based on user input, if the requested data is not requiring old data, you will only query to main table, if your user need historical data, then you would open both table or only the "old stuff" table.

Another thing to check and make sure is how your table is indexed. You can speed up your queries by indexing the most often used fields. This take some HDD space, but this can significally improve performance on large table. But, I know there is side effects to that, but I'm not sure about all the PROS/CONS.

I think your best bet, would be to automatically move old record in a new table that would contain only old record. This process could be done via an automatic script, or you could add a step upen record addition... Something like this :

AddNewRec($new_record_info);
MoveRecToHistory($how_long);
GetRecord($parameters);

This way, everytime someone add a record, your DB would automatically move old data to the history based on your "how-long" you want to keep data in your history.

And you could use a safer approach, by running the MoveRecToHistory($how_long), every day around 00:00, this way data would always be in the correct table even during vacation/holidays/week-end/etc...

Hope this gave you good tips/ideas.

-
GA-K8NF-9 / <b><font color=green>Athlon 64 3200+</font color=green> @ 3800+</b>
Infineon DDR400 (CL2.5) 2x512Megs
<font color=green>GeForce 6600GT 128Megs</font color=green>
<A HREF="http://www.getfirefox.com" target="_new">Get Firefox!</A>
 
indices (or indexes, depending on who/what you're asking :lol: ) Would be my first suggestion.

I'm unfamiliar with MySQL and PHP, but I'm pretty experienced at optimizing queries generally though, as I've been working with Interbase/Firebird for a few years now.

Things that affect query execution times, in my experience (in order from most important to least important):
1)What Indices are defined
2)Query Design
3)Hardware

That said, it's possible to make truly terribly designed queries that take ages to run on even a well-designed DB.

If you can get Mysql to tell you what Query PLAN it's trying to use, that's usually a good place to start - see if it's using any "natural" joins. That almost always means an index would be handy.

If you like you could post/PM me a brief overview of the database design, and a problematic query and I'd have a look, but I wouldn't be able to respond all that quickly of course. :smile:

---
<font color=red>"Life is <i>not</i> like a box of chocolates. It's more like a jar of jalapeńos - what you do today might burn your a<b></b>ss tommorrow."
 
thanks for the tips guys...

I've done some twealking and it improved everything traumatically


it looked like the main problem was my original design of the query. it was basically doing redundant queries for some of the data.

I had basically for some obscure reason unkown to me done the query to pull out the array of the dates of all the pays, and then using that date, did another query in the same table to pull up the hours associated with that date.

the double query is what killed it.

what i've NOW done is one query to pull up both the feilds of Date and Hours, and just put a simple loop in there to display the next record in the query.

sometimes ya try too hard and do things and overcomplicate things. simplifying the query and just using the loop was the easiest method for some reason I thoujght it didnt originally work and used the long ass double query
 
>and it improved everything traumatically

Oh dear.. sorried to hear that LOL.

= The views stated herein are my personal views, and not necessarily the views of my wife. =
 
A little drama, a little trauma, so who's to know?

BTW, I had a crush on a girl in HS named Linda Pasternak, but she wouldn't even give me the time of day. That was traumatic. I wonder if she's related to m.
<P ID="edit"><FONT SIZE=-1><EM>Edited by endyen on 08/31/05 03:34 AM.</EM></FONT></P>
 
Talk about outdated info

This THGC forums is dog slow, whats it running on? I know the net bandwidth for thg is huge.

<A HREF="http://www.xtremesystems.org" target="_new">www.xtremesystems.org</A>
 
Well, if you look at the amount of posts in the 'Archive' forums, you'll see that we seem to be overdue for some maintenance in that area.

I guess there's probably been a server upgrade so it's happier with the increased amount, but all the same, it would probably help quite a bit :smile:

---
<font color=red>"Life is <i>not</i> like a box of chocolates. It's more like a jar of jalapeńos - what you do today might burn your a<b></b>ss tommorrow."
 
no relation

and i'm a very traumatic dramatic sorta git

but SQL / PHP is killing me. i'm hours away from deadline on this thing and I can't get one function working

need to upload a file to a directory and reference it in the SQL Database so that when certain profile is selected, that persons image is displayed
 
Can't you save the image or a link to the image in the table so it is part of the query?

I'm assuming that when a operation requests a certain profile it pulls:
1. First Name
2. Last Name
3. Address
4. ...
5. Picture

Its been awhile since I've done DB stuff but I know you can pull images. It seems like it would be equivalent to a query you run on New Egg. The Item picture as well as Item Details are displayed.
 
very simillar, i figured it out without even touching the database so no worries.

since every profile hasa unique identification number in our systems, when the user uploads an image file, it automatically renames the file to their unique ID and stores it in a directory on the server for it.

now when someone prints off a specific report, the image is printed inline automatically. i don't use anything in the SQL except the Unique id of the prfile and it finds the image
 
heck, i couldnt afford [-peep-]... nor would ym company pay it

I wrote this program because it would help out our jobs here. not because i'm paid to do it. i'm still being only paid my "office admin" salary
 
You must log in or register to reply here.

TRENDING THREADS

Latest posts

Moderators online

Share this page

COMPANY
RESOURCES
FOLLOW
Top Bottom