|
What about using EXISTS with a CASE statement? One of the nice things about EXISTS is that it only walks the index-- it doesn't have to retrieve a result set. In this case, it sounds like you are just wondering whether a child record exists or not.
SELECT
a.categoryId,
a.categoryParentId,
a.categoryName,
(
CASE
WHEN EXISTS(SELECT * FROM categories b WHERE b.categoryParentID=a.categoryID)
THEN 1
ELSE 0
END
) AS categoryHasChildren
FROM categories a
WHERE
a.categoryParentID=2
OR
a.categoryID=2
Especially if you are often-reading and seldom-updating the number of children, you may want to consider adding an extra column to each record containing the number of children. Then you could use triggers to update that column on the parent record when you add or remove a child.
Thank you.
Jeff Varszegi
|
|
|
|
|
This seems to be a good and fast solution.
Thanks for your feedback.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
You're welcome!
Jeff Varszegi
|
|
|
|
|
I might be wrong but my SQL statement does only one SELECT but yours does two SELECT.
I have no idea how SQL server handles that, but is there a posibility that my version is faster than yours?
I am not sure about this...
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
Like I said, an EXISTS statement is special. It's not really a SELECT because it doesn't return a result set.
In general, you need to worry about table accesses (which may lock a table), use of indexes, doing extra work etc. when you worry about query performance. In this case, you cannot escape the fact that you have to do a subquery of some type in order to look for child records! My way actually is faster because not only does it not return a result set, but it will stop looking after the first record; your original solution (I'm not slamming it, but just explaining) looks up all the child records by joining this potentially-large table to itself and doing an expensive GROUP operation.
I wasn't sure whether or not to leave in the extra WHERE clause to retrieve the children or not, so I left it in.
I recommend pulling up the query plan for each query so you can see what's going on; this should show you a pretty good breakdown of the work done by the server. After you get done with that, you should realize that the proof is in the pudding; if you're curious about the real-world difference, do a microbenchmark!
This is as simple as making a Transact-SQL script that calls GETDATE(), loops through a certain amount of times calling a script, calls GETDATE() again, and does math to figure out the approximate time for each operation. Test a suitably large number of operations to give decent precision, and also put more than one call in each loop to minimize looping overhead. Lastly, select the results into variables, instead of returning a result set to the browser (due to inefficiencies in Query Analyzer). I usually make a stored procedure to perform a microbenchmark if I'm that curious.
Now that I think of it, one way to really speed up your performance (and provide a nice layer of abstraction from the database in the bargain) is to wrap your query in a stored procedure. This greatly reduces the parse/compile burden on the server.
It's great that you don't just take my word for it! I'm curious to hear of your results.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks for taking the time and explaining all this to me. I shall experiment next week or over the weekend at home as I will be leaving in a while. Although I have been programming in ASP for a long time, I never really thought about queries. Now we are developing large projects at work and we have to make sure that our queries are fast.
Thanks again.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
I have a sql databse..
Now I'll have to write a program that will find the users for that database and each user's access access permissions.
and set permissions iff necessary!
can someone help me out in doing this!
ranjani
|
|
|
|
|
You can use the SQLDMO to view user information on a particular database. If you are using something like C# or VB .NET, you can just add a reference to your project to the SQLDMO. Then just start referencing the objects you want to use. SQLDMO.User (which can be accessed from the SQLDMO.Database.Users collection) will probably give you what you're looking for.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
I'll have to program using c++ ...using UI's but!
What is SQLDMO ?
ranjani
|
|
|
|
|
Here is a basic definition of SQLDMO that I found here: http://www.expresscomputeronline.com/20030616/techspace1.shtml
"DMO is a set of objects that represents the SQL Server infrastructure. This is a very rich and complex object model. It is not intended to provide data handling, which is best done by traditional SQL commands.
DMO is designed for administration of SQL Server using a component object model approach. In fact, using DMO, you can do things that are impossible with Enterprise Manager. Moreover, it is often much simpler to write a DMO based code rather than trying to find equivalent Transact SQL syntax for achieving certain administrative tasks."
Things I've used it for include:
- Listing available SQL Servers
- Listing available databases in a given server
- Programmatically "walking" a set of database tables for generating database objects based on those table
(just to name a few). It is a very useful component. I found a link here http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q287/7/37.ASP&NoWebContent=1 that demonstrates listing available SQL servers using C++. I'm sure there are many more examples out there, so you should just do a google search for the term "C++ SQLDMO" or something like that. There tend to be more examples out there written in VB or C#, however, the same component can be referenced from C++.
Let me know if you need any further clarification.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
I have two tables called master and child tabels.For each record in the master table i have to get the information in corresponding child table.I am using the same connection.SO, when i am trying to get information from child without closing the DbReader in Master table(I need master DbReader also.So, I am intensionally not closing it) it is giving me error that the connection is already in use.
|
|
|
|
|
In ADO.NET, you can only have one command executing against a connection at a time. The DataReader has exclusive use of a connection while it's open.
You can do one of two things: you can either open a second connection to execute the 'child' commands, or you can use a DataSet to store the results of the two commands. The first option is very wasteful of resources. The second option allows you to set up DataRelation objects which allow you to navigate the child rows using the DataRow object's GetChildRows method.
If you plan to do the second, you might as well ask for both tables in the same query batch, generating two result sets.
You could also perform a join on the server side, depending on what you actually need to do with the data.
|
|
|
|
|
Thanks. I solved the problem using the Datasets. But I have another problem . I want to read BLOB from Database. When i am using DataReader I have a function Called GetBytes which will serve my purpose. But now i am using DataSet.Can u tell me how can i Read the BLOB from a DataSet?
|
|
|
|
|
How to change the user assocuiated with dbo.
The problem is that when users enter stuff personal to them and I use the default USER (without quotes) it enters correctly DOMAIN\USERNAME, but my records insert just "dbo".
I thought that changing the owner to the admin account this would be solved.
|
|
|
|
|
When I insert a date value into Database, I can use either “Insert into TableA values(‘” + date + “’)”, or “Insert into TableA values(#” + date + “#)”.
Could you show me the difference of two SQL above?
Thanks!
xyz
|
|
|
|
|
You can't do this in SQL Server, at least with Transact-SQL. Here's a test I just ran:
CREATE TABLE #test (
dt DATETIME NULL
)
INSERT INTO #test VALUES ('2003-01-01')
INSERT INTO #test VALUES (#2003-01-01#)
On the second value, I get this error:
Server: Msg 128, Level 15, State 1, Line 1
The name '#2003' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
What language and database are you using?
Thank you.
Jeff Varszegi
|
|
|
|
|
I believe that the #01-02-2003# version is a Microsoft extension that assumes that the date uses MM-DD-YYYY format (i.e. US date format). The other format uses the default country-specific format for the computer.
In England, I commonly see PCs that are configured with control-panel defaults of "MM-DD-YYYY" or "DD-MM-YYYY" - so if you are using MS-Access and are hard-coding dates then the hash-version is probably the safer way to go. With SQL-Server, I always use the ISO "YYYY-MM-DD" format.
If you are executing the insert statement using an ADO command then I normally add parameters to the command-object because that handles casting from the original data-type, and it prevents SQL-injection attacks.
Andy
|
|
|
|
|
Hi all,
I have xml document with sql query that get results from SQL Server 2000, data returned can contain verious languages -Russian in my case- when i access the xml document directly from within IE it returns the results fine with Russian data encoded correctly.
Now i want to use ADO Stream object to access and execute the same xml document, the Russian data returned as question marks (?).
I tried to set the Charset proprty of Stream object to many values, including UTF-16, ISO-8859-1, Windows-1251 ... but nothing work, i tried also to set enocding property of the xml document to same values above, also same problem.
What should i do? also if database contains Indian or Japanese data, whats the solution?
Thanx in advance.
|
|
|
|
|
Hi,
I am using ADO to query my Access database and need to run one sql query that conditionally issues one of two complex select statements depending upon the value of a particular field in a table. The two select statements query different tables. I'm trying to use something like:
SELECT IIF((SELECT PropertyValue FROM PropertyTable WHERE PropertyCode=10)=0,
(SELECT ...), (SELECT ...))
But this only lets me return one column, whereas my select statements need to return many columns. The CASE statement seems to have the same limitation.
Any advice would really help!
Joanne
|
|
|
|
|
Sub-selects can only ever return a single value if used in the way that you have tried. If you absolutely need to meet your requirements with a single SQL statement then you may try something like:
SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
WHERE PropertyCode=10)=0)
UNION ALL
SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
WHERE PropertyCode=10)<>0)
ORDER BY ...The first select returns a resultset if property 10 does not exist, otherwise the second select returns its resultset. Note that both resultsets must return the same number of columns, with the same datatypes.
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy, this does exactly what I was trying to do.
Works like a charm.
Joanne
|
|
|
|
|
I get the following error when I compile a project.
C1083: Cannot open type library file: 'C:\\Program Files\\Common Fi les\\SYSTEM\\ADO\\msado25.tlb': No such file or directory<br />
Error executing cl.exe.
I got MSADO20.tlb and MSADO21.tlb in my machine, but not MSADO25.tlb
Is this right location[^] to download MSADO2.5?
As per MSDN, 2.8[^] is the latest version.
What's the diff between MSADO 2.0, 2.1, 2.5 and 2.8?
TIA
"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".
|
|
|
|
|
If you're running Windows 2000 and haven't obtained MDAC 2.6, the type libraries for version 2.5 are kept in msado15.dll. The latest versions are always found in msado15.dll; the .tlb files are shipped with each new version of MDAC in order to target the previous versions.
For example, I have MDAC 2.7 SP1 installed on this Win2k machine, so I have msado20.tlb, msado21.tlb, msado25.tlb and msado26.tlb.
If you're using SQL Server 2000, you should install at least MDAC 2.6; since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.
You can check your version of MDAC using MDAC Component Checker[^].
|
|
|
|
|
Mike Dimmick wrote:
the type libraries for version 2.5 are kept in msado15.dll.
 I thought version 2.5 will be kept in msado25.dll
The error says the following file not found
'C:\\Program Files\\Common Files\\SYSTEM\\ADO\\msado25.tlb'
Mike Dimmick wrote:
since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.
I don't have the SQL Server 2000 right now but I will be installing it soon. Will this installation takes care of MDAC 2.6?
Mike Dimmick wrote:
MDAC using MDAC Component Checker[^].
Thanks for the link. From this tool, I got MDAC 2.1 SP2 and MDAC 2.5 SP3..
"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".
|
|
|
|
|
What's the difference between:
FULL LEFT/RIGHT JOIN and just LEFT/RIGHT JOIN?
What does the word "FULL" tells the query analyzer? THanks.
norm
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
