|
thats the main point - if columns a and b are indexed you will notice a huge % difference in performance - the number of I/O's will reduce even more.
|
|
|
|
|
blakey404 wrote: thats the main point
It's a point. I don't see how it is the main point. The OP asked about performance for a query. One aspect is indexes, another is returning just the information that is actually needed. Both equally valid, especially as there was no filtering (WHERE clause) involved.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
i would say its the main point because of how the indexes are stored. if the columns specified to be returned are both in a non-clustered index the data returned can be retrieved directly from that index, therefore not having to do x number of disk reads to return all the columns, as with a select *. try both in query analyzer and see the difference. returning index columns only from a non-clustered index is the fastest query possible, as it reduces logical reads.
|
|
|
|
|
blakey404 wrote: returning index columns only from a non-clustered index is the fastest query possible, as it reduces logical reads.
I don't disagree with that. I'm just saying there are other ways that can help improve performance. Indexing is not the be all and end all of performance improvement.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
oh i completely agree, i was just thinking about in this particular example - where it is very possible the 2 columns required may be indexed.
 )
|
|
|
|
|
Like Colin said about the network bandwidth, it will take less to only pull the fields that you need at the moment, and it is better for design and maintenance of the code to only pull what you need at that moment in the program. Another thing, do you need to pull the millions of rows at once?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
No, I don't, as I process the data programmatically from VC++. See my reply to Colin above.
I am not sure the bandwith usage is more for query #2 if queried as above, right?
|
|
|
|
|
Query two shouldn't take up as much bandwidth as query one. The extra bandwidth depends on how many fields are being fetched from the table.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
hi! i have a table 'Categories' whose schema is:
CategoryId(Primary Key) int
Name nvarchar(25)
i have another table 'Products' whose schema is:
ProductId(Primary Key) int
CategoryId int
Description nvarchar(250)
ProductImage nvarchar(50)
UnitCost money
now i want whenever i delete an category from Categories, then all the data related to this Category from the table Products should be deleted automaticaly from Products table. How is possible?
Thanks & Regards,
SAMir Nigam,
Software Engineer,
STPL, Lucknow, India.
|
|
|
|
|
You have to do cascading deletes. Google Cascading Delete[^] has some good results that can help you out 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
First delete rows in Products table with categoryid whichyou want tobe deleted then delete category from categories table.
example
delete from products where categoryid='1'<br />
delete from categories where categoryid='1'
I Love SQL
|
|
|
|
|
He could always have that in a stored procedure, but cascading delete is easier 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I have four tables (t1, t2, t3, t4) in my DB. Given t1 and t4, i want to find what tables(relationship) are between t1 and t4. I want to do this using SQL and/or .NET. Any suggestions?
t1 t2
--------- ---------
t1Id (pkey) t2Id (pkey)
t2Id (fkey) t3Id (fkey)
<br />
t3 t4
--------- ---------
t3Id (pkey) t4Id (pkey)
t4Id (fkey)
The result in this example would be t2, t3
|
|
|
|
|
Start by looking at your primary keys.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi all,
I am in a fix for which i really need some input with this problem
colA-------- --COLB
1000-------- --,,,--EXCESS OF ,,,
1000A------- --A,,
100AB------- --A,B,,
100ABC------ --A,B,C,
This result was derived to achive the table Below
DESIRED RESULT
colA-------- --COLB
1000-------- --NULL
1000A------- --A
100AB------- --A
100AB------- --B
100ABC------ --A
100ABC------ --B
100ABC------ --C
BUT THIS IS THE RESULT I GET
colA-------- --COLB
1000-------- --NULL
1000A------- --A
1000A------- --NULL
1000AB------ ---A
1000AB------ ---B
1000AB------ --NULL
100ABC------ --A
100ABC------ --B
100ABC------ --C
THIS IS BECAUSE THE FIRST RESULT CONTAINS ESCESS OS ,,, I WANT TO
REMOVE THOSE AND HAVE ONLY THE VALID LETTERS,
IS THERE A WAY TO REMOVE ONLY THE EXCESS COMMAS
ANY INPUT BE VERY HELPFUL
THANKS
SQL DEVELOPER
|
|
|
|
|
I recommend a regular expression that looks for 1 or more contiguous commas and replaces with a single comma. I can't give you the regex straight away, but maybe you can find one on a regex site somewhere.
|
|
|
|
|
I am using MS Access to make a simple database and i used a lookup field to another table so that the user can update the lookup as needed. But when i copy the records to another table i want them to copy as text data not lookup data. I would like to know how to copy it to another table and get the actual value instead of the foriegn key.
|
|
|
|
|
How can I get more control over generating SQL scripts? I would like to optionally include drops where SSMS only allows drops OR creates etc. I would like to script as alter not create etc.
|
|
|
|
|
Perhaps Red Gate SQL Compare? We use it for generating scripts to update a schema, rather than drop/recreate.
|
|
|
|
|
Hai,
I have Windows XP SP2 and I have installed SQL Server 2005. while i'm opening SQL sever Management studio i get a pop message indicating that "SSPI Error Context". Can anyone sort out this problem.
aanryn
|
|
|
|
|
Hello!
I am trying to retirve data from almost five tables simultaneously, but having problems in use of a single query!!!
Here is the sample of thouse five tables
students: stuid, classID, sectionID, termID, sessionID, stuName, other fields
Classes: classID, className
Sections: sectionID, sectionName
Terms: termID, termName
Sessions: sessionID, sessionName
NOw if i want to extract data with a single query in somewhat this form
stuName, className, sectionName, termName, sessionName
Can someone help me in this, as to how the best query can be formed?
thank you!!
-- modified at 1:28 Monday 30th July, 2007
|
|
|
|
|
SELECT stuName = s.stuName,
className = c.className,
sectionName = sc.sectionName,
termName = t.termName,
sessionName = ss.sessionName
FROM students s
LEFT OUTER JOIN Classes c ON s.classID = c.classID
LEFT OUTER JOIN Sections sc ON s.sectionID = sc.sectionID
LEFT OUTER JOIN Terms t ON s.termID = t.termID
LEFT OUTER JOIN Sessions ss ON s.sessionID = ss.sessionID
Eliz.K
<awww.oin1.com< a="">
|
|
|
|
|
Thank You Eliz!
One thing more, also if i want to get the record of a single student, can i used the where clause after joins like
where s.stuid = '2222'
???
thanks!!!
|
|
|
|
|
|
I am calling a store procedure on sql server 2000 table as below::
return SqlHelper.ExecuteDataset(cn, "abc",54);
It is giving the following exception...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding error.
can anyone suggest me the solution plz..
hiiiiii
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
