|
Hello Everyone,
What is the difference between join and nested queries.......
and which is executed fast
-----------------------------------
|
|
|
|
|
The Knowledge wrote: What is the difference between join and nested queries.......
and which is executed fast
A join, ummm... joins two tables together.
SELECT * FROM A INNER JOIN B ON A.PK = B.FK
A nested query (aka sub-queries) runs one query inside another.
SELECT * FROM (SELECT * FROM A) -- Okay this is a bit of a useless example
I wouldn't say that either is faster than the other. Although, from what I can see, it is easier to make a mess of nested queries which will affect performance negatively. Typically a nested query will be used with a join anyway.
SELECT * FROM A<br />
INNER JOIN (SELECT * FROM B) AS nestedB ON A.PK = nestedB.FK
Okay, that's another pointless example, but it does demonstrate the basic syntax. Often, the sub-query will be used with some sort of group by clause.
|
|
|
|
|
Hi,
it is very hard to find a general answer on your question and it really depends on the situation and database schema behind (indexes at most). If you are trying to join two tables with, let's say more than 30 millions of records it will take forever, but if you have a good filtering on the second one and than using that in subquery it will fly. On other way, in a lot of situations, for example, SQL Server will treat your subquery as separate one and perform a separate optimization plan which costs time. There are two different types of subqueries, self-contained (run independently of the outer query) and correlated ones (have references to outer query columns) and they also behave differently. The same is for joins. SQL Server now has a couple of join algorithms used by query optimizer (loop join, merge join and hash join) in different situation.
To get a real answer in a particular situation, is possible by just examine execution plan for both queries and than decide which one to use.
In addition to this, I would always consider using CTE (Common table Expressions) (at least on SQL Server 2005) as one of the most effective ways of subquerying.
Hope this helps a little bit.
Best regards,
DLM@bypsoft.com
|
|
|
|
|
can we use arrays in a sored procedure
if not, is there any alternative to store the values somewhere dynamically from where we will be able to use the values.
|
|
|
|
|
nicetohaveyou wrote: store the values somewhere
Will a temp table suffice ? (With respect to SqlServer)
|
|
|
|
|
can we use a temp table dynamically inside a T-SQL statement
|
|
|
|
|
You can, and you can also use a table variable. This is, effectively, an in memory temporary table.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
I am using Sql Reporting Services 2000 viewer in my asp.net1.1 page.
The report which i developed is showing results perfectly.I opened my application in another browser and submited some new data thru some interface .When i generate the report again the data is not showing in the report viewer. But when i logout and login into my application and generated report and the data is getting displayed. Can anyone tell me the cause for this problem.
Thanks in Advance
Phanindra...
|
|
|
|
|
Check the properties of the report it sounds like you might be caching the data or using report snapshots?
|
|
|
|
|
Hi all,
I'm making use of C++ to connect to a ODBC Oracle database, but when I do, I receive the following error:
(I'm making use of Microsoft's ODBC driver for Oracle)
[MOD]
I have have made an entry within my tnsnames.ora
my_database =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = my_database)
)
)
I have tried to ping the DSN and got the following:
C:\>tnsping MSORA
C:\>tnsping MSORA
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 14-SEP-2
007 08:28:33
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
H:\ORACLE\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pcname.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = X
E)))
OK (20 msec)
[MOD]
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve the connect identifier specified
Many Thanks in advance
Regards,
-- modified at 2:15 Friday 14th September, 2007
-- modified at 2:20 Friday 14th September, 2007
-- modified at 2:29 Friday 14th September, 2007
The only programmers that are better that C programmers are those who code in 1's and 0's ![Green Alien | [Alien]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/alien.gif)
 Programm3r
My Blog: ^_^
|
|
|
|
|
|
i want to access a database stored in sql 2005 stored in a different machine through DSN from a different machine in a network.
How do i do that?
|
|
|
|
|
|
Hi Everyone.
I'm finding it hard to get information of verifying variables passed into a stored procedure. Specifically if the value is NULL then I want to abort the procedure and let VB.NET know the insert failed without issuing an exception.
I found some information that gave me the following IF statement but the layout is not like VB so I'm not sure where to go from here...
IF (@RateLevel1 IS NOT NULL) OR (@RateLevel2 IS NOT NULL)
NOTE: I borrowed the following code from a tutorial video so if you find any issues please let me know.
ALTER PROCEDURE dbo.sptblBillingRatesInsert
(
@Description nvarchar(150),
@RateLevel1 money,
@RateLevel2 money,
@RateLevel3 money,
@RateLevel4 money,
@RateLevel5 money,
@BillingLevelID int OUTPUT,
@ModifiedStamp timestamp OUTPUT
)
AS
SET NOCOUNT ON;
-- Inserts a row in Billing Rates
INSERT INTO tblBillingRates
(Description, RateLevel1, RateLevel2, RateLevel3, RateLevel4, RateLevel5)
VALUES
(@Description, @RateLevel1, @RateLevel2, @RateLevel3, @RateLevel4, @RateLevel5);
-- Checks if the last statement produced an error
IF @@ROWCOUNT>0 AND @@ERROR=0
SELECT @BillingLevelID = BillingLevelID, @ModifiedStamp = ModifiedStamp
FROM tblBillingRates
WHERE (BillingLevelID = SCOPE_IDENTITY());
|
|
|
|
|
hi,like this?
IF (@RateLevel1 IS NOT NULL) and (@RateLevel2 IS NOT NULL) ...
begin
raiserror 'parameters null value!'
return 1 --fail
end
else
begin
insert into ...
return 0 --success
end
|
|
|
|
|
Why do you want to do this? If you have designed your system properly, then you should have code in place at a much higher level to prevent this from happening. It is much better to warn the user that there is a problem and give them a chance to do something about it before you attempt the insert, which would just be an unnecessary trip to the database.
In general, it is much better to validate input as close to the user as possible.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Pete,
I was watching the Microsoft VB training videos by Beth Massi. She showed two things:
- using stored procedures server side to make the app more secure
- using VB code on the dataset to check data and issue error providor status for that column.
These items are supposed to keep the "business logic" away from the forms and in a central location rather than re-keying in different forms.
Well I put it into practice and it works nicely, BUT ONLY IF you type the right things in....but as we all know users rarely do as they should. I can break the system easily if I tab through all fields in the datagrid. The error provider flags them as needing attention (NULL ETC) but once you reach that last field it causes an unhandled exception. My instinct is to handle the excpetion on the form using an event like leavecell or dataerror (not sure on names sorry) BUT then I'm splitting my logic between the dataset, stored procedure and the form....and breaking the rules of centralising....
Hence I thought maybe I should look to do it in the stored procedure....but as you say that means the user doesn't get a mice message and warning......so confused
|
|
|
|
|
A "typical" design would be to have a user interface where you would identify which elements of the data absolutely had to be present and validate that they were there before you could do anything else. Once the data has been validated, it is then passed to a business layer. Again, the business layer would be responsible for validating the data. Finally, the data would be passed over to the data layer.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I validate at both locations. This is especially necessary when you code in a team. The DBA is responsible for making sure, to the best of his/her ability, that garbage is not put into the system and that the user/developer knows why there is a rejection of input.
The front end developer should make sure that they limit the amount of rejections to the best of their ability. I find that checking inputs on both sides is highly beneficial and reduces DB errors dramatically.
The only time I stray from this is when the data needs to be verified against other existing data in the database. Here I make the DBA sole responsibility so that verfication data does not need to go back and forth across the wire.
|
|
|
|
|
The way you have it does not allow nulls so you may just want to handle the exception msg 201 as a null parameter. The other way is to add a default @RateLevel1 money = NULL and have an output parameter @IsNull of type bit and set to true if any parameters are null.
|
|
|
|
|
update @lia_table1<br />
set vou_id = replace((select voucher_id from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1)<br />
and voucher_no in (select max(voucher_no) from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1) group by bkng_no))<br />
where ids = @minsum + 1,'con','dom')
This is giving me error near where .
actually i want this replace the voucher_id 'con_ccg_199' with 'Dom_ccg_198'
|
|
|
|
|
soniasan wrote: This is giving me error near where .
I guess your where clause(where ids = @minsum + 1) is outside the outerquery
<br />
(select voucher_id from confirm_detail where .....)
If it needs to be along with the outerquery, you might be in need of AND clause instead of WHERE.
Also, looks to me like the query is muddled up with respect to the inner query's termination.
Probably executing the inner-queries individually might help you analyse the query in a better way.
HTH
|
|
|
|
|
If you're using SQL-Server then I think that the following may work for you:
UPDATE A SET
vou_id = REPLACE(B.voucher_id, 'con', 'dom')
FROM @lia_table1 A
INNER JOIN confirm_detail B
ON B.bkng_no = A.bkng_no
AND B.voucher_no = (
SELECT MAX(voucher_no) FROM confirm_detail C
WHERE C.bkng_no = A.bkng_no)
WHERE A.ids = @minsum + 1Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I don't - I'm not keen on the blatant attempts to get MVP status.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: not keen on the blatant attempts to get MVP status
You got my 5 for your attitude
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
