Database Discussion Boards

First Prev Next

Speed consideration

amitcoder835-Jun-07 21:20

amitcoder83

5-Jun-07 21:20

Between Stored procedure and user defined function which is faster and why?

Re: Speed consideration

Colin Angus Mackay5-Jun-07 22:20

Colin Angus Mackay

5-Jun-07 22:20

They solve different problems. A speed comparison is invalid.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Speed consideration

Puneet Sri5-Jun-07 23:23

Puneet Sri

5-Jun-07 23:23

Stored procedure is faster then user defined function because stored procedure is precompiled and user define function is compiled at the of call
So it should take little more time to respond.

I hope this will help you

Puneet Srivastava

Re: Speed consideration

Mike Dimmick6-Jun-07 0:53

Mike Dimmick

6-Jun-07 0:53

Not true in SQL Server. SQL Server compiles the query plan of any query, including a stored procedure, the first time it is used. It then caches that query plan, giving a higher weighting to the plan for a stored procedure over an ad-hoc query with parameters, over an auto-parameterized ad-hoc query, over a query that couldn't be auto-parameterized.

If you want to force a stored procedure to be recompiled, use WITH RECOMPILE on the EXECUTE statement. To force a recompile every time, use WITH RECOMPILE on the CREATE PROCEDURE statement.

I believe the text of a user-defined function is effectively incorporated into the text of the query that called it, it does not get a separate query plan of its own. This may depend on whether it's a scalar function, an in-line table-valued function or a multi-statement table-valued function.

Stability. What an interesting concept. -- Chris Maunder

Re: Speed consideration

Arun.Immanuel6-Jun-07 3:55

Arun.Immanuel

6-Jun-07 3:55

http://qa.techinterviews.com/q/20060804232021AA6mjm4[^]

Regards,
Arun Kumar.A

find duplicated rows

rkherath5-Jun-07 18:56

rkherath

5-Jun-07 18:56

hi,

i have an sql table which has no primary key. i want to find out wether the same row is repeated. how can i see it.

regards
Ruwandi

rkherath

Re: find duplicated rows

ganesamoorthidhayalan5-Jun-07 21:18

ganesamoorthidhayalan

5-Jun-07 21:18

select field_name count(*) from tablename where group by field_name order by 1;

This query to find out the number of duplicate value.

Re: find duplicated rows

ganesamoorthidhayalan5-Jun-07 21:29

ganesamoorthidhayalan

5-Jun-07 21:29

its also the best query

SELECT *
FROM BB1
WHERE (DATE_STAMP,DB_NAME,TABLE_NAME) IN (SELECT DATE_STAMP,
DB_NAME,
TABLE_NAME
FROM BB1
GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
HAVING COUNT(* ) > 1);

Re: find duplicated rows

rkherath6-Jun-07 0:18

rkherath

6-Jun-07 0:18

hi ganesamoorthidhayalan,

it works. thanks a lot.

Regards
Ruwandi

rkherath

HELP...SQL problem

xuyuan5-Jun-07 17:48

xuyuan

5-Jun-07 17:48

I'm using SQL to output some data from the 3 table below (Sales, Product,& Staff).
How if i want to display a table as this...Have any ideas??

ProductId TotalSale Sale_of_team1 sale_of_team2 sale_of_team3
P012 3 0 3 0
P016 27 7 0 20
P017 4 4 0 0
P019 2 0 2 0
P022 2 0 2 0

>>Sales
ProductId Date Week SalesUnit StaffId

P012 20070514 20 3 S002
P016 20070531 22 7 S001
P016 20070531 22 20 S003
P017 20070531 22 2 S001
P017 20070523 21 2 S001
P019 20070530 22 2 S002
P022 20070502 18 2 S002

>>Product
ProductId ProductName Category Cost

P012 ADSL 512M/256K Streamyx 77
P016 LT 38 Lets Talk 38
P017 PLAN A Lets Talk 68
P019 PLAN C Lets Talk 198
P022 Italk Mobile-50 Italk Prepaid Cards 50

>>Staff
StaffId StaffName Team Level

S001 Jass 1 1
S002 kwee 2 2
S003 Rain 3 2

Thank for helping...arigatou gozaimasu!!!

Re: HELP...SQL problem

andyharman5-Jun-07 23:14

andyharman

5-Jun-07 23:14

The following should do the trick:

SELECT Sales.ProductId,
   SUM(Sales.SalesUnit) AS TotalSale,
   SUM(CASE Staff.Team = 1 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team1,
   SUM(CASE Staff.Team = 2 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team2,
   SUM(CASE Staff.Team = 3 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team3
FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
GROUP BY Sales.ProductId

The "case" clauses allow you to conditionally summate a column.

Or

SELECT Product.ProductId,
   (SELECT SUM(SalesUnit) FROM Sales) AS TotalSale,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 1
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team1,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 2
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team2,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 3
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team3
FROM Products

This uses correlated sub-queries to do the work.

Regards
Andy

convert varchar to date

rkherath5-Jun-07 17:32

rkherath

5-Jun-07 17:32

hi All,

i have a dates field which contains dates as varchar type

Dates

(yyyymmdd)
20071231
20070312
20071123

i wanna convert this in to

yyyy/mm/dd

2007/12/31
2007/03/12
2007/11/23

how can i do this please tell me

convert(varchar,dates,101)

didnt include the '/'

regards
Ruwandi

rkherath

Re: convert varchar to date

Krish - KP5-Jun-07 17:40

Krish - KP

5-Jun-07 17:40

to get the dates with "/" seperated you can use
substring(dates, 1, 4) + '/' + substring(dates, 5, 2) + '/' + substring(dates, 7, 2)
But the dates remains varchar

convert function actually converts to datetime data type.

Regards
KP

Re: convert varchar to date

rkherath5-Jun-07 18:55

rkherath

5-Jun-07 18:55

hi,

thank. i got it .

thx a lot.

regards
Ruwandi

rkherath

SSRS;Minus Sign

Amit Kumar G5-Jun-07 16:34

Amit Kumar G

5-Jun-07 16:34

I need to change the display from (123.98) to -123.98 in SSRS.

I tried couple of fromat option on text box and still no good results...

Any Help!!

Amit

Calling Ref Cursors in Data Access application blocks

kjosh5-Jun-07 16:21

kjosh

5-Jun-07 16:21

Hi,

I have one procedure in Oracle which is returning the REF Cursor I am accessing it by using the Data access application block DAAB like the following:

OracleParameter param = new OracleParameter();

param.ParameterName = "Emp_Cursor";

param.OracleType = OracleType.Cursor;

param.Direction = ParameterDirection.Output;

param.Size = 3000;

Database db = DatabaseFactory.CreateDatabase("DBConnection");

//DbCommand cmd = db.GetSqlStringCommand("Select * from emp");

DbCommand cmd = db.GetStoredProcCommand("emppackage.select_employee");

db.AddOutParameter(cmd, param.ParameterName, param.DbType, param.Size);

DataSet ds = new DataSet();

ds = db.ExecuteDataSet(cmd);

GridView1.DataSource = ds;

GridView1.DataBind();

But iam getting this error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

How to call Ref Cursors in DAAB.

Thanks in advance.

SSIS: Changing app.config settings for a custom dll called from the script task

fodonnel5-Jun-07 13:46

fodonnel

5-Jun-07 13:46

Hi,
I am using a custom .NET DLL from within a script task in SSIS. I want to override some configuration settings in the the DLL (app.config settings). Can I do this is SSIS and if so how?

Thanks,
Fergal.

How to run a stored procedure from within a stored procedure

AndyDev5-Jun-07 13:41

AndyDev

5-Jun-07 13:41

Hi,

I have two stored procedures that returns a SELECT result.

I want to be able to return this results, from a third stored procedure, according to a parameter.

Something like this:

IF (@DisplayUnknown = 0)<br />
    BEGIN<br />
        EXEC stpSnapshotNormal(@Type, @dtDate)<br />
    END<br />
ELSE<br />
    BEGIN<br />
        EXEC stpSnapshotUnknown(@Type, @dtDate)<br />
    END

This of course does not work.
Does anyone knows how is it possible to make work?

Thanks!

Re: How to run a stored procedure from within a stored procedure

Krish - KP5-Jun-07 17:52

Krish - KP

5-Jun-07 17:52

the problem is parameters are enclosed with in parenthesis

use this way
IF (@DisplayUnknown = 0)
BEGIN
EXEC stpSnapshotNormal @Type, @dtDate
END
ELSE
BEGIN
EXEC stpSnapshotUnknown @Type, @dtDate
END

Regards
KP

Re: How to run a stored procedure from within a stored procedure

AndyDev5-Jun-07 18:04

AndyDev

5-Jun-07 18:04

Thanks, for some reason I missed that!
Hate when it happens Wink | ;)

Re: How to run a stored procedure from within a stored procedure

Krish - KP6-Jun-07 1:37

Krish - KP

6-Jun-07 1:37

Welcome

Regards
KP

SQL problem

kozu5-Jun-07 12:35

kozu

5-Jun-07 12:35

Hi,
I have a table that looks like this:

Name Job Salary
Jim Eng 50000
Jane NULL 100000
Bill NULL 55000
Bill Dev 55000

I need a query that would strip out the rows that have NULLs for 'Job' if there's a row with the same 'Name' with a non NULL 'Job'.
The result would look like this:

Jim Eng 50000
Jane NULL 100000
Bill Dev 55000

I could easily do this in C# after I bring all the rows in, but I thought it would be more interesting to get the right data with a query.
Any ideas?

thanx

Re: SQL problem

andyharman5-Jun-07 23:19

andyharman

5-Jun-07 23:19

SELECT * FROM MyTable<br />
WHERE Job IS NOT NULL<br />
OR Name NOT IN (SELECT NAME FROM MyTable WHERE Job IS NOT NULL)

Re: SQL problem

kozu6-Jun-07 5:04

kozu

6-Jun-07 5:04

thanks Andy, that was it!

Exporting ERD from Visio 2007 Enterprise Architect to SQL Server 2005

salman_syed_015-Jun-07 6:10

salman_syed_01

5-Jun-07 6:10

Hi can u help me?
i have created an Entity Relation Diagram in Visio Enterprise Architect 2007. Now i want to export the ERD from Visio to SQL Server 2005.I don`t know how to do that?
I need help on this stage.
Thanks.

Last Visit: 31-Dec-99 18:00 Last Update: 21-May-24 19:46

Refresh

ᐊ Prev 1...1539 1540 1541 154215431544 1545 1546 1547 1548 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.