|
ASALAMOALEKUM All,
i am working on virtual community and i am struct in btw...
actuall i have to maintain friend network, in this regard i have a Friend table in my sql database which contain columns of User_id and Friens_id (none of them is primary)
example: if table view is:
serial# User_id Friend_id
0 001 007
1 001 003
2 002 009
3 003 005
4 004 002
5 005 010
now if user 001 visits the profile 0f user with id 010 so as a friend network information it shud be shown "001->003->005->010"
the retrieval of this(which is between quotes) information is creating problem for me, can anyone suggest me a sql query for this... ??
reply as soon as possible!
i wud be very thankful!
Sana
$@N@
|
|
|
|
|
What database are you using? SQL Server, Oracle, something else?
The need to optimize rises from a bad design
|
|
|
|
|
i am using SQL server
$@N@
|
|
|
|
|
|
|
Thanks alot!
i will read it and will inform u if my problem is get resolved through this...!!
thnks again!
if you know any ebook based on virtual community making using asp.net with c# and sql server please recommend me!
regards,
God bless you!
SANA AHMAD
$@N@
|
|
|
|
|
There is a "ent.tbl_NetworkProfileTemplate" table in the system
with the following columns
PK_NetworkProfileTemplateId,
ProfileTemplateName,
ProfileTemplateDescription,
FK_TenantId
For TenantId =1 there are about 50000 records and for TenantId = 2 there are 1000000 records.
There is also another table tbl_Tenant which contains information about the tenant.
Now When I execute the following statement by logging in with tenant = 1
select * from ent.tbl_NetworkProfileTemplate
where ent.tbl_NetworkProfileTemplate.FK_TenantId
= ( select PK_TenantId FROM ent.tbl_Tenant WHERE TenantDBUserName = System_User)
This query runs very fast takes less than second to execute and returns number of records = 50000
But if I execute the following statement it takes 45 seconds
select * from ent.tbl_NetworkProfileTemplate
where ent.tbl_NetworkProfileTemplate.FK_TenantId
= [ent].GetTenantId()
The only thing I am doing in this statement is calling a UDF GetTenantId which actually executes the statement
select PK_TenantId FROM ent.tbl_Tenant WHERE TenantDBUserName = System_User
I am not able to understand why the response time is so different.
|
|
|
|
|
You are calling the UDF for every single record, hence the extended run time.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks. Got it.
Can you please suggest how to call UDF only one time...is there any way?
|
|
|
|
|
It all depends on what it does. I have, where the UDF is not too complex, extracted the code and used it either in the select or used a temp table and done an update.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Assuming the UDF return 1 value then run the UDF first and load the return value into a variable. Or go back to the original version
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i was working with SQL Server 2000.recently i purchased a new laptop wherin i got preinstalled Windows Vista home with SQL Server 2005 installed.i find it very uncomfortable to work on it.
can any one tell me how can i install SQL Server 2000 in windows vista or otherwise how to create Databases and Tables in Vista.
Thanx a Lot
|
|
|
|
|
You can install SQL Server 2000 on Vista without problems, and without removing SQL Server 2005, but why not bite the bulet and stop using old technology - unless there is a very good reason.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good Morning
i have Migrated a DTS package from SQL 2000 to SSIS, and i have used BI to make changes and deploy it and i have set the Option of "Run64bitRuntime" to False and have deplyed my packages and when i run the packages i got an Error that said.
This task does not support native Win64 environment. Please run the package in 32-bit WOW environment instead.
i have Googled that and took the Solution that people had and on the Steps in the Job "Type" i choosed "Operating System (CmdExec)" and in the command i have
"F:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn" /SQL \RECREATE_PROPERTY_SUMMARY" /SERVER cenetsqld001 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and when i run the Job, i get the following Error
Message
Executed as user: TSHWANE\cenetsqld001sql. The process could not be created for step 1 of job 0x049B2E7908970C49A9BA5A5796E5AC30 (reason: 5). The step failed.
What is wrong
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hello everyone!
I am trying to convert the below sql into oracle but i am having some issues with the variable declarations.
ex.
declare @md date
set @md='7/1/2008'
select @md
in oracle how?
any ideas?
Thank you!
|
|
|
|
|
run this is SQLDeveloper and
set serveroutput on;
declare
md date default '01-JUL-08';
begin
select '01-JUL-08' into md from dual;
dbms_output.put_line( md );
end;
hth
Al
|
|
|
|
|
thank you for reply ,that was what i want!
|
|
|
|
|
thank you for reply ,that is what i want!
|
|
|
|
|
All of that just to output a date I'll stick to SQL Server
|
|
|
|
|
They both have their advantages and disadvantages...
hth
Al
|
|
|
|
|
Is this simplified if you use something like TOAD? While I have not used Oracle in a decade it was always lacking a good UI and we found TOAD the equivalent of the old SQL Query Analyser. Is this still the case.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You mean SQLDeveloper- it's an IDE from Oracle (FREE) - Never been a TOAD guy either but it does do explain plans, intellisense, run scripts and even do a diff between schemas.
hth
Al
|
|
|
|
|
J4amieC wrote: All of that just to output a date WTF I'll stick to SQL Server
I'll second that
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
is there really any difference between these things or they're just name differently in Oracle and SQL Server respectively for the same thing?
|
|
|
|
|
AFAIK basically they are the same thing. Different restrictions etc may apply, but the idea is to have an intermediate result set.
The need to optimize rises from a bad design
|
|
|
|