| Author |
Topic |
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
 Posted - 2004-12-01 : 01:30:04
|
I must confess that im new in SQL.I have this problem in querrying...I have 900,000 records to querry and it takes a lot of querrying time for me to execute my stored proc.Is there anyway to fasten my query? I've heard about using text file? please tell me how? and can you show me some references to fasten my query? I've also heard about indexing? please help me. Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-01 : 01:52:45
|
please post your query, someone might be able to help you if they can identify what needs to be identified. --------------------keeping it simple... |
 |
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 01:58:23
|
| Its just a simple select query.... SELECT * FROM tblMillionsOfDatas where mID = 'A112080' and mDate = (select getdate())whoever my querry is still slow coz it has to display 900,000 records...I have problem particularly on my querry time specially i have to use this query on report genaration.Someone told me that i must have knowledge in indexing and text file i think? Please show me any reference outof this concept.Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-01 : 09:44:46
|
| wait -- this particular QUERY returns 900,000 records, or the table it is quering FROM contains 900,000 records?if you are RETURNING 900,000 records in a report, thena) that's a pretty long report (thousands of pages)b) by definition, it needs to take a long time to run -- you are returning LOTS of data accross the networkNow, if you are returning significantly LESS than that number for this particular query, then make sure that you have indexes on mID and mDate. and by the way -- getedate() returns a date AND a time, so only rows that have mDate of EXACTLY the very second you run this query will be returned.- Jeff |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 19:11:49
|
| Thanks jeff...lets revise my query.SELECT * FROM tblMillionsOfDatas where mID = 'A112080' and mDate = (select convert(char,getdate(),101))Yup! this query returns 900,000 records, I have problem to fasten this query. I have heard about using indexes and text file on handling this query that is why im looking for a help for me to better understand that technique.Any references will be much appriciated. Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 19:33:49
|
| Who told you that text files would help out? What are they referring to? And yes of course indexes will help, but this query will NEVER be fast if it has to return 900k rows. Who even can process that many rows in a report?Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-01 : 19:46:59
|
| I can't imagine what you are trying to display the 900l rows with, but you'll probably need more hardware on the sql server, and a lot more RAM on the machine displaying the 900k rows.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 19:47:31
|
| Thanks Tara...I don't want to ask them coz they want to let me find it my self. Yes they say text file can do. Something querying on a text file? im not sure about it. Please scold if me if necessary. They say something manipulation on text file. But im not sure.Tnx.Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-01 : 19:49:08
|
| Do you have an index on mID? Is it a Clustered or Non-Clusterd Index?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 19:49:50
|
| Thanks Michael...Hardware i think can be set aside first...Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-01 : 19:53:08
|
quote:
I don't want to ask them coz they want to let me find it my self. Yes they say text file can do. Something querying on a text file? im not sure about it. Please scold if me if necessary. They say something manipulation on text file. But im not sure.
Not a scolding, just some advice: whoever this is is either pulling a joke on you, or they are profoundly misinformed. I'd recommend not taking any advice from them. |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 19:53:38
|
quote:
Originally posted by MichaelP
Do you have an index on mID? Is it a Clustered or Non-Clusterd Index?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Sorry Michael, i don't used index or Clustered or Non-Clustered. I confess that I don't have ideas on this things. That is why im asking for any references. Ill just keep posting here to upgrade my knowledge in SQL.Thanks to you guys here...Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 20:21:34
|
| Thanks for your concern robvolk...Any good references on Indexing and Clustered or Non-Clustered concept?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-12-01 : 20:24:30
|
Do you really need:SELECT * .... Maybe specify the columns that you need.Thanks,Jon |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-01 : 20:28:08
|
| Books Online will get you started on the basics of indexing. Clustered vs. non-clustered is not really an issue though. Read about them, but don't think that it will solve the problem. Which is, as Tara and Michael mentioned, the fact that you're trying to send 900K rows to a client application. There is absolutely no way you can do anything to make this perform well, except to drastically cut down the number of rows you're trying to transfer. |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 20:43:09
|
quote:
Originally posted by jhermiz
Do you really need:SELECT * .... Maybe specify the columns that you need.Thanks,Jon
Yes! There are 9 columns and all of them are valuable information.Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 20:47:35
|
| Well his point is that SELECT * is a performance issue. It's the least of your worries though. But for future reference, don't ever use SELECT * unless it's in an IF EXISTS or IF NOT EXISTS. Write the columns out:SELECT Column1, Column2, Column3...FROM Table1WHERE...Tara |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 21:00:08
|
| Thanks Tara...I'll put it on a note. So it's indexing could somehow make my query faster?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-01 : 21:07:29
|
| Yes, you'll need an index on that mID field.Open up Enterprise Manger and find that table. Right Click the table and select "All Tasks" and then Manage Indexes.Click NewFor the Name, Type IX_TableName_FieldName (replace your table and field names in there)Click the checkbox beside the mID fieldIf it will allow you, check the Clustered Index box.Click OK At this point, it should take some time to create the index. You should see your SQL server going crazy until it's done.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-01 : 21:20:37
|
| This is really pretty insane. Can I ask you what your specs are? why are you writing a query that returns 900,000 rows for a report? what is this report supposed to look like? how much pages long do the consumers of this report expect it to be?if the goal is to take 900,000 rows of information and return a summary for a report, are you aware you can do stuff like this:select SalesPersonID, sum(salesAmount) as TotalSalesfrom Salesgroup by SalespersonID??in the above example, suppose you have 30 salesmen, and you have 100,000 rows in the Sales table. But for a report, you want to see total sales by salesperson. Thus, the above query just returns 30 rows and gives you the totals of all 100,000 rows for the "salesAmount" column.so, what is the end result of this query you are working on the the users are expecting?- Jeff |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-12-01 : 21:36:29
|
quote:
select SalesPersonID, sum(salesAmount) as TotalSalesfrom Salesgroup by SalespersonID
yes! i can do stuff like this...its for a purpose jsmith8858.Im generating a report of transaction for this comming year end. And that transaction is about 900k of records. I used Select * from tblMillionsOfDatas is just the same with select c1, c2, c3....i used * because all columns in the table is necessary to be displayed...Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
Next Page
|
Handling about 900,000 records