|
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
|
|
|
|
|
Good Afternoon Guys
Am Accessing the table from another link Server in a Join and this gives my ASP Classic application an Error , here is where the Join is happening
SELECT DISTINCT
FARM_BOUND.OBJECTID AS BOUNDARY_AREA_ID, LB.CLASS AS CLASS_ID,
LB.GENERAL_PLAN_NO AS GP_NUMBER, LB.REMARK AS REMARKS,
LB.DATE_CAPTURED, LB.DATE_MODIFIED,
LB.PROCLAMATION_DATE AS DATE_PROCLAIMED, FILEMAP.REGION AS SG_REGION,
sde.VW_BOUNDARY_AREA_CLASS.CLASS AS CLASS, BC.EXTENSION, BC.NUMKEY AS NUM_KEY,
FARM_BOUND.GEOCODE AS GEO_CODE, FARM_BOUND.NAME, FARM_BOUND.NAME AS BOUNDARY_AREA, FARM_BOUND.STATUS AS STATUS_ID,
sde.VW_BOUNDARY_AREA_STATUS.STATUS,
CASE WHEN LB.MUNICIPAL_STATUS = 0 --changed from VARCHAR to INT 2005-11-24 --'0'
THEN 'WITHIN'
--ELSE
-- CASE WHEN LB.MUNICIPAL_STATUS = 'WITHIN'
-- THEN 'WITHIN'
ELSE 'OUTLYING'
-- END
END AS MUNIC_STATUS
FROM sde.VW_BOUNDARY_AREA_STATUS
RIGHT OUTER JOIN sde.FARMBOUNDARY_PLUS_LANDBOUNDARY FARM_BOUND
ON sde.VW_BOUNDARY_AREA_STATUS.STATUS_ID COLLATE SQL_Latin1_General_CP1_CI_AS = FARM_BOUND.STATUS COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN CENETGIS001.Surveyor_General.sg_admin.SG_INDEX FILEMAP
RIGHT OUTER JOIN CENETGIS001.SDE.sde.LANDBOUNDARY LB
LEFT OUTER JOIN sde.VW_BOUNDARY_AREA_CLASS
ON LB.CLASS COLLATE Latin1_General_CI_AS = VW_BOUNDARY_AREA_CLASS.CLASS_ID COLLATE SQL_Latin1_General_CP1_CI_AS
ON FILEMAP.SG_NO COLLATE Latin1_General_CP1_CI_AS = LB.GENERAL_PLAN_NO COLLATE SQL_Latin1_General_CP1_CI_AS
ON FARM_BOUND.GEOCODE COLLATE Latin1_General_CP1_CI_AS = LB.GEOCODE COLLATE Latin1_General_CP1_CI_AS
LEFT OUTER JOIN CENETGIS001.SDE.SDE.BOUNDARY_CODES BC
ON BC.GEOCODE COLLATE Latin1_General_CI_AS = FARM_BOUND.GEOCODE COLLATE SQL_Latin1_General_CP1_CI_AS
i have added the Collation methods as you see , but still giving me an Error
Msg 447, Level 16, State 0, Procedure VW_BOUNDARY_AREA, Line 7
Expression type int is invalid for COLLATE clause.
and in my Application it Shows
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Thank you
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
|
|
|
|
|
The int datatype has no collation - one of your types must be an integer...
|
|
|
|
|
What is the difference between IS NULL, = 'NULL' in MS SQL. When I query a table to get the first 10 rows with the following condition:
SELECT TOP 10 *
FROM TableA
WHERE ColumnX IS NULL
It gives me random rows, not the first 10 rows. ColumnX is all nulls but I need the first n rows. I noticed when I use this condition then I always get the first 10 rows:
WHERE ColumnX = 'NULL'
I know IsNull is a function but what is the difference between the other two?
|
|
|
|
|
= 'NULL' always evaluates to true...
|
|
|
|
|
Do you really mean = 'NULL' (in quotation marks)? If so, 'NULL' is just a string whereas would be 'ABC' so it doesn't have anything to do with the reserver word NULL in SQL.
If you mean = NULL (without quotation marks), that condition based on ANSI standard is always false (nothing is equal to NULL.
In different databases this may vary and in SQL Server the logic can be modified in database settings.
The need to optimize rises from a bad design
|
|
|
|
|
Mika Wendelius wrote: If you mean = NULL (without quotation marks), that condition based on ANSI standard is always false (nothing is equal to NULL.
Actually, they can evaluate to (unknown)
check this article[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
