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.
| Author |
Topic |
|
vivekchandra9
Starting Member
5 Posts |
 Posted - 2005-06-03 : 16:24:35
|
| Below is the query I am running mostly on my SQL server database. I get the results in about 15 mins. The database is 300 gb and with 800 million records. Can any1 give some sugestions on how to improve query response time? Also please provide some recommendations on system cofiguration like RAM and CPU speed needed. Currently there is 4 gb of RAM and 8 gb processor. There are other application running on the server too.Thanks.DECLARE @Date1 varchar(10)SET @Date1=CONVERT(varchar(10),GETDATE()-1,101) SELECT CONVERT(varchar(10),TransactionHdr.TransDate,120) AS TheDate ,TransactionHdr.StoreID AS StoreNum ,SUM(TransactionDtl.Qty/10) AS SaleQty , TransactionDtl.ItemID AS SKU , REPLACE(TransactionDtl.UPC, '000000000', ' ' ) AS UPC , SUM(TransactionDtl.ItemAmt) AS SalePrice , TransactionDtl.DeptID As Department FROM TransactionHdr Inner JOIN TransactionDtl ON TransactionHdr.TransactionHdrId = TransactionDtl.TransactionHdrID WHERE CONVERT(varchar(10),TransactionHdr.TransDate,101) = @Date1 TransactionDtl.Qty <> 0 AND TransactionDtl.ItemAmt > 0.00 GROUP BY TransactionHdr.TransDate ,TransactionHdr.StoreID , TransactionDtl.UPC , TransactionDtl.ItemID , TransactionDtl.DeptIDORDER BY TransactionHdr.StoreID |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-03 : 16:57:45
|
| Only 4GB of RAM for a 300GB database! And other applications are consuming this memory. You need to max out the RAM on your machine. If your machine only supports 4GB of RAM, then you need to purchase another server that can support way more. With a database this large, you need to look into partitioning. SQL Server Books Online has information about this. If you could show us the execution plan, the DDL of the tables including indexes, then we might be able to improve your query.Tara |
 |
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-03 : 17:13:48
|
| Without seeing the QEP or the DB here are a few things that stick out:Why are you converting that GETDATE to varchar? That will sure kill you ... The hdrID should be your pkey and/or clustered index - I'm assuming it's setup that way. Also, I'm assuming you have an index on that date field.If possible, ditch the REPLACE in the select - functions like REPLACE, DATEPART, etc... will kill the performance of a database that large.Jayme |
|
|
|
vivekchandra9
Starting Member
5 Posts |
Posted - 2005-06-03 : 17:52:52
|
| Each table has like around 50 clolumns out of which only primary keys and foreign keys are indexed. How much RAM would suggest and is processing speed also needed more than 8GB? How can I get rid of "Replace" ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-03 : 18:07:38
|
| It's hard for us to suggest an amount of RAM or speed of the CPU or the number of CPUs. I'd get as big of a box as your company can afford though due to the size of the database. If only the PKs and FKs are indexed, I'd suggest rethinking this as you are probably quering on other columns. And are you sure that the FKs are indexed? It is a common mistake to think that an index is automatically added when you add an FK constraint. This is the case with PKs but not with FKs.Tara |
|
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-03 : 18:16:59
|
| Ok, I doubt that date field is indexed...Put an index on that field. Don't convert to varchar.Other than that - you will have to post the schema information about your database. 4GB of ram is a little low for your setup ... Is the database recovery model on 'simple' out of curiousity? 300 gigs on full, woah! ;)Jayme |
|
|
|
|
|
|
|
|
SQL server performance issues