January 15, 2011 at 11:00 pm
Dear Friends
I am working with a huge database which has a table named table2 with 4,63,22,505 records and this has only two columns namely ID1 and ID2
I have one more table named USD which has 20 thousand data in it .
So the structure of Table2 goes like this
ID1 Nvarchar
ID2 nvarchar
USD Table
Field3 nvarchar(50)
Field1 nvarchar(50)
Field2 text
Field4 text
Field5 datetime
Field6 numeric(29, 0)
Field7 numeric(29, 0)
Field8 numeric(29, 0)
NoOf numeric(29, 0)
Desc text
my query goes like this
select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From USD
join table2 on ID2=Field1
January 15, 2011 at 11:33 pm
Your DDL isn't very descriptive. Did you create the database? Column names like IDn, Fieldn are very hard to work with as a developer. You also haven't shown any keys or indexes, and your data types are suspicious.
Regardless, it looks like you are looking for the group by operator
select t1.ID2, count(*)
from USD usd
join Table2 t2 on t2.ID1 = usd.Field1
group by t1.ID2
January 16, 2011 at 3:38 am
That database design looks terrible. Whoever 'designed' it apparently didn't know much about normalisation.
Anyway, the key to fast data retrieval is good indexes and well written queries. What indexes do you have? Do you really need results from the entire table (all 4 million rows)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply