|
SELECT Company FROM Orders
-or-
SELECT DISTINCT Company FROM Orders
Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways?
Thanks guys! 
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
SELECT Company FROM Orders will list every record, IOW
ABC
XYZ
ABC
EFG
GHI
XYZ
SELECT DISTINCT Company FROM Orders will list only unique matches IOW
ABC
XYZ
EFG
GHI
Have a look at the SQL Server SELECT statement article, nice 
"There are no stupid question's, just stupid people."
|
|
|
|
|
Thanks!
I guess the real question is how to prevent the complete parsing of the
records. Is there a way to tell SQL to "find the first occurence then stop"?
Luc
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Well, this depends on your data.
Speaking from a purely SQL Server perspective the basic SELECT is the fastest, but if the DISTINCT clause eliminates a large amount of records it might be faster when taking the middle tier, network access, aso (basically the rest of your processing...) into consideration.
The thing to remember is that a DISTINCT is post processed. So the SQL Server actually makes a complete SELECT first, and then filters the records.
Morty
|
|
|
|
|
Here's what I want to do:
I have a table that contains words and there are no duplicates. Right now,
this is what I do:
SELECT * FROM table WHERE word = 'hello';
This will cause the SQL server to parse the entire table to look for records
that match the criteria. But since I'm 100% sure that the word 'hello' is
only there once in the table, I want the query to stop as soon as it finds
the record.
What can I use for this kind of job?
Thanks for your help! 
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
What can I use for this kind of job?
I think what you're looking for is "SELECT TOP 1" but I'm not an expert. Give it a go.
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
If the 'word' column is indexed as unique, SQL Server will not have to look through the entire table and will only need to search the index (very fast) to find the desired row.
If the 'word' column is indexed as non-unique, SQL server will still search the index, but may need to return more than one row.
If the 'word' column is not indexed SQL server will do a table-scan (full table search) and return every match.
You can use the SELECT TOP 1 bla.bla.bla syntax, but probably what you want is for SQL Server to have a unique index on the 'word' column.
|
|
|
|
|
If you're using SQL Server you can use Query Analyzer to find out how long it took to run the query, and where the greatest cost was incurred. It's an incredibly useful tool.
--
Paul
"I need the secure packaging of Jockeys. My boys need a house!"
- Kramer, in "The Chinese Woman" episode of Seinfeld
MS Messenger: paul@oobaloo.co.uk
Sonork: 100.22446
|
|
|
|
|
DISTINCT is in general slow (depends on actual data of course). Whenever you use DISTINCT, SQL server needs to group result set (by sorting them) on DISTINCT columns. In case there's no index on those columns, it will take time. On the other hand, if result set is huge, but it contains a lot of repetitions on DISTINCT columns, eliminating repetitions may have positive effect on speed of transferring filtered results to the client.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
When you run two concurrent-ish updates through Jet from different users of the same program, there seems to be a significant (~5 secs) delay before either one can see the other's update.
This, as I understand it, is a delay in the Jet drivers before updates are actually flushed to the database. Is this right?
If so, is there any way to force a flush without the performace hit of stop-starting the connection (pref. using ADO)?
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
try setting the following connection property:
.Properties("Jet OLEDB:Implicit Commit Sync") = True 'force synchronous write
|
|
|
|
|
I use the Class CDAORecordset in my program, and it can run correctly on the computer whith VC++ Installed, But it can't Run on the computer whithout VC++ Installed.
Isn't the lack of the DLL files?
VCBeginer
|
|
|
|
|
You need MDAC.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Actually MDAC 2.6 and later do not contain Jet drivers any more See link[^]
|
|
|
|
|
Hi all
SCary, my 1st post in this forum.
Does anyone have some .NET classes that can fill a table in database with valid random data up to x records. I wanna do some analyzing on my tables but entering data manually is absurd
Thanks
"There are no stupid question's, just stupid people."
|
|
|
|
|
No, but what a very cool idea  .
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
Perhaps I can still enter this month article competition 2 days... not much to it, I imagine... although 2 finger typing takes it toll on lengthy lines, not to mention paragraphs
Cheers and nite 
PS: You are the lucky person to get post 1k from me 
"There are no stupid question's, just stupid people."
|
|
|
|
|
leppie wrote:
You are the lucky person to get post 1k from me
You call that 1K? I call it 24 short 
But I'll be sure to return the compliment in 175 messages time
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
Paul Riley wrote:
You call that 1K? I call it 24 short
"There are no stupid question's, just stupid people."
|
|
|
|
|
1K=1024
Go to sleep 
Paul
I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
|
|
|
|
|
leppie wrote:
valid random data
This would be quite simple to do if you don't have any business constraints on the data.
Can you post the table schema?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
SimonS wrote:
Can you post the table schema?
What exactly is that?  OK, I know what it is, but where/how/what format, etc, can I get it from somewhere or do I have to type it out IOW ?
Cheers
"There are no stupid question's, just stupid people."
|
|
|
|
|
If you're using SQL, try this little easter egg nugget of functionality:
select the table from the listin enterprise manager (single click)
CTRL-C (copy into clipboard)
CTRL-V into notepad
voila! There's the table definition.
Cool, huh?
Which DB are you using btw?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
Ooo thanx
SimonS wrote:
Which DB are you using btw?
MSDE , but as you guessed I have installed the client tools from some BackOffice 2000 CD 
BTW, being new to this whole SQL thing, do you have a good link or 2? Not on SQL usage, but focusing more on information, like table structure and stored procudure parameter discovery. MS has certainly done a shitload of work in that department regarding VS.NET Just a pity, all them nice classes are marked private 
Cheers
"There are no stupid question's, just stupid people."
|
|
|
|
|
i want to change the appearence of the report so that the details section`direction is horizontal not vertical.
Ex: if the details contains field ID normally it is displayed as
1
2
3
i want it to be : 1 2 3
does anyone here know how can i do this?
Emporer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
