|
Hi,
I need some experienced advise on a situation that i am facing. Please provide help.
I have a products excel sheet. currently 20,000 products but they keep on increasing. i am asked to develop the sql tables. the products can be divided into 2 types. One type have about 30% rows whereas other will have 70%.
In database implementation, i can have one table with type field which separte the 2 types also I can have 2 different tables for both types.
My question is :
Between the two, for searching purposes which proves the best one.
ie. on average 1 table search provides better performance or 2 tables provide better performance.
thankyou
|
|
|
|
|
Hard to say whether you will get better performance with searching 1 large table or 2 smaller tables without knowing the details, but with the number of records you are talking about (less than 100,000) you will be amazed at how fast SQL server can return results from a query compared to searching in Excel.
My suggestion is to store the data in one table, with a "type" idicator. Creating the proper indexes will also improve performance depending on the nature of the query.
Question: Do all of the columns for each product "type" apply ? Another way of asking this question is that if you created 2 tables; one for each product type, would they have the same columns ? If so, then for sure, go with 1 table and an idicator field.
BTW: What type of application will be accessing this data ? ASP.NET or a Windows client application ?
|
|
|
|
|
Thankyou for responding
Do all of the columns for each product "type" apply?
theoretically , both should have same detail. but does the number of columns matter. What i mean is that if product has 12 columns and i split it into two tables.
1. productname
2. productdetail
would select statement make a difference? to my knowledge, its the same thing.
the application would be used by asp.net. Also if u don't mind, it would be nice if u mention whether its wise to use mysql db or sql server db. is sql server n asp.net better, or is sql server or php better, or asp.net n mysql or other way round.
Thanks again.
|
|
|
|
|
Better go with a single table with a bit field for product type.
Maintaining 2 different table for the same identity is not a good dB design practice.
The performance wont be an issue with the size of record that you are saying.
Implement indexes on the columns that you will use in the query conditions.
Now regarding the selection of database, for ASP.Net, better go for MS-SQL Server. As .Net framework provides native driver for MS-SQL Server. This is the managed driver and performance is better.
However, the hosting cost for MS-SQL Server is higher, if budget is a constraint, you can go for mySQL.
|
|
|
|
|
|
You actually need 2 tables
Product
ProductType
for when they add a 3rd product type. As said by others create the product table with all the details and the a foreign key to the product type table.
100k rows is nothing to a database, add 2-3 zeros and you MAY have performance issues to address.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I need to query and do a comparison on a versioned data table with a separate table dedicated to the version specific information.
The versioned data is related to pricing and the comparison is based on the date of service for the product sold. I need to find the correct price based on the version "effective date". However, there may be multiple entries for the selected product that are within the Effective date range. In this case, I need to use the record with the highest (or most recent) Version Date.
My product billing information is housed in one table. My referential pricing data is housed in another table and the version ids and dates are located in another table.
Any ideas?
|
|
|
|
|
We had the same situation with effective pay rates for employees.
I believe the structure of the table was something like this:
EmpID, Rate, EffFromDate, EffToDate
The query would be something like:
Select rate
from ratetable
where EmpId=ID and targetDate => EffFromDate and targetdate <= effTodate
Example, new employee:
123,$10.00,1/1/2000,12/31/2999
Example, pay increase on May 1, 2009
123,$10.00,1/1/2000,4/30/2008
123,$12.25,5/1/2008,12/31/2999
Notice that the most recent record should always have the artificially high "EffTodate" of 12/31/2999.
You could also make the oldest record equal to the employee's hire date instead of some artificial low date of 1/1/2000.
Best of luck.
David
|
|
|
|
|
Need Query for this requirement. The data in my table is as follows
Zip Blac White green
123 1.76 2.04 1.00
234 3.49 2.39 3.24
Need Columname as output if the column value is > 2 for given zipcode.
For example.if i give zip as "123" the query has to find out the value among the columns which is > 2. i.e White
If i give zip as "234" then the query has to find out the value among the columns which is > 2.
But here all values are > 2. So you have to pick the max value. i.e 3.49 = Blac
G. Satish
|
|
|
|
|
What have you managed to come up with so far?
I'd imagine there are quite a few different solutions to this problem so if you show what you have so far I'll try and help from there.
|
|
|
|
|
Hi,I hope i understood you good enought. Here is TSQL solution which I hope will works for you.
select * from mytable<br />
<br />
declare @zip as int<br />
set @zip = 123<br />
declare @white as varchar(100)<br />
set @white =(select white from mytable where zip=@zip)<br />
<br />
declare @black as varchar(100)<br />
set @black =(select black from mytable where zip=@zip)<br />
<br />
declare @green as varchar(100)<br />
set @green =(select green from mytable where zip=@zip)<br />
<br />
create table #temptable (valstr varchar(100),colname varchar(100))<br />
<br />
insert into #temptable values (@white,'white')<br />
insert into #temptable values (@black,'black')<br />
insert into #temptable values (@green,'green')<br />
<br />
declare @colname as varchar(100) <br />
set @colname=( select top 1 colname from #temptable order by valstr desc )<br />
drop table #temptable<br />
<br />
declare @selectquery as nvarchar(max)<br />
set @selectquery ='select '+@colname+' from mytable where zip='+cast(@zip as varchar(10))<br />
exec(@selectquery)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
I have a table with 2,000,000 records and there is no way for me to filter it by range.
I was able to get the first 1,000,000, my problem is to get the next 1,000,000 without any duplicates from the first 1M records. Any Idea.
SELECT TOP 1000000 * from Table1
how about the next 1M??
Pls..
Dabsukol
|
|
|
|
|
Select top 1000000 colname from
(
Select top 2000000 colname from urtable order by colname desc
) T
order by colname asc
G. Satish
|
|
|
|
|
In short
For First 1000000
Select top 1000000 colname from urtable order by colname ASC
For Next 1000000
Select top 1000000 colname from urtable order by colname DESC
ZAK
|
|
|
|
|
Oops just added another record - this is a Microsoft answer, technically correct practically useless.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select top 1m records order by ID
Get the last ID in the first set (you do have an ID field)
Select the top 1m records where ID > last ID
You can also look into Row_Number in SQL Server, this will eliminate the detect requirement but is more complex in the select. I would assume you are processing the 1m records and therefore detecting the last ID would be trivial.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using postgreSQL 8.0 (pgAdmin III). Whenever I create a trigger, an error "There is no in-built function 'funtion name'".
What the actual problem is with the trigger or postgresql?
Please send me the solution..
Thank u.
|
|
|
|
|
Hey,
I want to access letters in strings selected from DB.
I would like to do something like that:
SELECT word FROM dictionary
WHERE
-- and now the pseudo-code part:
word[0] is in {'a','e','o','u','y'}
AND word[1] is in {'b','c','d'}
where word[0] means first letter in selected word, word[1] second letter etc.
Is it possible to do in MS SQL?
|
|
|
|
|
Look into CHARINDEX and SUBSTRING
pseudo code ...
IF (CHARINDEX(SUBSTRING(WORD,1,1),'aeiou',1) > 1) then
found it
End if
|
|
|
|
|
big thanks!!! I haven't test it yet but it seems to be the solution 
|
|
|
|
|
You should use the Like keyword:
select word from dictionary
where word like '[aeouy][bcd]%'
Wout Louwers
|
|
|
|
|
Wow ! That is a neat way of using the "like" comparison. I like it! 
|
|
|
|
|
You got my 5.
Briliant.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
iam having two sqlservers .where i want to move entire table from the server1 database with the data to anothers server(server2) datbase using query .plz guide me......
regards
venu
modified on Friday, July 24, 2009 5:55 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
