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 |
|
gllen
Starting Member
7 Posts |
 Posted - 2006-03-20 : 19:35:35
|
| I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is.To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated). On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :)So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE") A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field C. If it's a new price it will insert a row into ItemPrices for that itemAlso, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster?Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much! |
|
|
gllen
Starting Member
7 Posts |
Posted - 2006-03-20 : 19:41:07
|
| Is there a way to edit? This should read:I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... WHERE lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", |
 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-20 : 20:16:24
|
Check whether following logic works if exists(Select * from ... where .. = <parameter>)Begin Insert into ... Values ..... Insert into ... Values .....EndElseBegin Update ...... Insert into history_table ... Values .....End |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-21 : 10:34:23
|
| "Is there a way to edit?"Yup, you can edit your own posts by pressing the icon that looks like "Sheet of paper + Pencil""INSERT... ON DUPLICATE KEY UPDATE"We do this by first "bulk importing" the data to a temporary/staging table.Then we delete anything present in the main database that is missing from the BUlk Import file (obviously only do this if the Import file is 100% of the data!)Then we do an UPDATE for items that already exist on the PK but are DIFFERENT in the actual data columns. We have a (massive!) where clause that only allows rows that are different to be updated. This keeps the Log size down, and helps performance. If all rows in the Import file will be guaranteed to contain data which is different tot he main data then just a JOIN will do.Then INSERT the new data. We do this last so that it is NOT also subject to the UPDATE.Kristen |
|
|
|
|
|
|
|
|
Best way to approach this