|
|
|
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
|
|
|
|
|
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?
|
|
|
|