Database

Table-value function performance

9-Jan-07 11:53

Never trust the wizards. They are at best useful only for elementary cases and learning.

kmaclean9-Jan-07 9:51

kmaclean

9-Jan-07 9:51

Can anyone comment on the performance pros/cons of using table-valued functions vs. Stored Procedures in SQLServer? Specifically:
1) are the execution plans for the functions stored as they are in stored procedures?
2) When using table variables inside the function, does the entire result set automatically wind up in memory, or can it be streamed using a reader?

Thanks for any input.

Opposite of the SQL "IN" statement?

dspyank9-Jan-07 6:17

dspyank

9-Jan-07 6:17

Does anyone know if there is an opposite to the SQL "IN" statement?

I have a string of values that I want to check the database for.

However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND"..

Is there anything out there?

Thanks and sorry for the trivial question.

Dave Smile | :)

Re: Opposite of the SQL "IN" statement?

Chris Meech9-Jan-07 6:24

Chris Meech

9-Jan-07 6:24

The opposite of "IN" would be "NOT IN", but I don't think that is what you are after in this case.

I don't understand your question wrt. "AND". If I query a column called "NAME", I can have returned all names IN ("MIKE", "CHRIS", "DON"). But there is no way any data item will ever be equal to all three of those values, which is what you are asking for when you say you want an "AND" condition.

Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]

Re: Opposite of the SQL "IN" statement?

kmaclean9-Jan-07 11:32

kmaclean

9-Jan-07 11:32

It sounds like "IN" is what you want. If I have a list of values, i.e. ('Apple', 'Orange')
and I want to know if they are in the FRUIT table, I would write:
SELECT Name
FROM FRUIT
WHERE Name IN ('Apple, 'Orange').

This would pull back all values from the fruit table that are in my list.

However, there is no logical way to use this with an "AND", because a string can only match one value.

If you're looking for the value 'AppleOrange' you might use the LIKE operator:

SELECT Name
FROM FRUIT
WHERE Name LIKE '%Apple%'

This would pull back values "Apple" and "AppleOrange"

I don't think there's a way to use the LIKE operator with a list though.

Hope that helps a bit.

Re: Opposite of the SQL "IN" statement?

Saud AKhter16-Jan-07 20:22

Saud AKhter

16-Jan-07 20:22

Hi Dave,

I think u want to select the rows if table contains all the values in your IN statement otherwise no row should be returned. If this is the case then unluckily there is no direct way to cope with this but fortunately you can accomplish this by SP.

for example (pseudoCode)

1. variable boolean allValues
2. loop for all value in your IN statment
3. Variable rowcount = select count(*) from table where column = 'your value'
4. if rowcount = 0 then allvalue = false and exit loop else continue with the loop
5. end loop
6. if allValues = false returned no row else returned allrows for the values in your IN statement.

Hope that works if this is what you are trying to explain

regards
Saud

saud

Procedure with multiple parameters and multiple Select Statements

rodney.fetterolf9-Jan-07 3:48

rodney.fetterolf

9-Jan-07 3:48

If someone could give me some guidance with the following problem. I have a table; tblItems; which fields fldItem(nvarchar 75), fldNSN(nvarchar 16), fldPartNum, fldVendor, fldPrice... I would like to have a stored procedure that can search and return a dataset based upon which parameter the user enters. I thought if I created a procedure like this, it would work but I keep getting some error in building the procedure Incorrect Syntax Near the Keyword 'Select' or Invalid operator for datatype. Operator equals modulo, type equals varchar;
Create Proc usp_SearchItems @pName nvarchar(75), @pNSN nvarchar(16), @pTPart nvarchar(20), @pVPart nvarchar(50) AS Declare @pQuery nvarchar(1024)
if @pName<>''
set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldItem Like '%' + @pName + '%'; Order By fldItem
if @pNSN<>''
Set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldNSN Like '%' + @pNSN + '%';

exec @pQuery

I don't have a problem if I use Create Proc usp_SearchItem @pName nvarchar(75) As Select * From tblItems Where (fldInactive=0) and fldItem Like '%' + @pName + '%'

I'm trying to use one procedure instead of four.

Any assistance would be greatly appreciated.

Re: Procedure with multiple parameters and multiple Select Statements

andyharman9-Jan-07 5:29

andyharman

9-Jan-07 5:29

Try the following:

CREATE PROCEDURE usp_SearchItems
    @pName NVARCHAR(75),
    @pNSN NVARCHAR(16),
    @pTPart NVARCHAR(20),
    @pVPart NVARCHAR(50)
AS BEGIN
    DECLARE @pQuery NVARCHAR(1024)

    SET @pQuery = 'SELECT * FROM tblItems WHERE (fldInactive = 0)'
    IF (@pName<>'') BEGIN
        SET @pQuery = @pQuery + ' AND (fldItem LIKE ''%' + REPLACE(@pName, '''', '''''') + '%'')'
    END
    IF (@pNSN<>'') BEGIN
        SET @pQuery = @pQuery + ' AND (fldNSN LIKE ''%' + REPLACE(@pNSN, '''', '''''') + '%'')'
    END
    SET @pQuery = @pQuery + ' ORDER BY fldItem'

    --PRINT @pQuery
    EXEC(@pQuery)
END

If you have any problems then uncomment the "PRINT" statement to find out what the final contructed query was.

The "REPLACE" function is being used to protect the stored procedure against quote characters. Do a search for "SQL Injection Attacks" to find out more about this problem.

Regards
Andy

Re: Procedure with multiple parameters and multiple Select Statements

M.H.1.2.314-Jan-07 21:46

M.H.1.2.3

14-Jan-07 21:46

You could use something like this. Default the parameters to null then coalesce or inull the parameter. The example proc below returns one result set but if you wanted to you could just as easily return one result set per parameter passed in.

Create Proc usp_SearchItems
@pName nvarchar(75) = null,
@pNSN nvarchar(16)= null,
@pTPart nvarchar(20)=null,
@pVPart nvarchar(50) =null

AS
Select *
From tblItems
Where (fldInactive=0)
and fldItem Like '%' + coalesce( @pName , fldItem) + '%'
and fldNSN Like '%' + coalesce( @pNSN , fldNSN)+ '%'
....

What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

Nick1977_209-Jan-07 3:06

Nick1977_20

9-Jan-07 3:06

Can anyone help me why "N" is used before any property (eg. "IsUserTable")in OBJECTPROPERTY in Sql Server ?

Re: What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

Pete O'Hanlon9-Jan-07 3:36

Re: What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

9-Jan-07 3:36

It converts the string into a Unicode string.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Rob Graham9-Jan-07 3:46

Re: What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

9-Jan-07 3:46

Strictly speaking, th N' doesn't itself "convert" the string, but rather specifies that it should be converted (if needed) and stored/retrieved as a Unicode string by the server...

Pete O'Hanlon9-Jan-07 4:11

Re: What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

9-Jan-07 4:11

True. I should be more careful with my grammar.

Rob Graham9-Jan-07 6:45

Re: What is "N" in OBJECTPROPERTY(id, N'IsUserTable') = 1)

9-Jan-07 6:45

Nick1977_209-Jan-07 17:39

Nick1977_20

9-Jan-07 17:39

Thanks a lot

.Net 2.0 Table Adapter best practice

Adam Crawford9-Jan-07 0:58

Adam Crawford

9-Jan-07 0:58

Hello,

I was hoping someone could give me a bit of information on the way I should be creating / handling my table adapters.
I have a persistant business object that is managing a number of database calls using a lot of different table adapters. The question I have is what the best practice is for creation of these table adapters. Is it better to persist a reference to each of my different table adapters over the life of my business object (i.e. create them all once, store them in class variables, then dispose of them all at the end) or is it better to create them as they are required (i.e a new table adapter created for each function call).
If someone could give a bit of an insight into what a table adapter is doing in terms of the connections created for it during instantiation that would be most helpful in helping me decide the most efficient approach.

Thanks for your help,
Adam

Rob Graham9-Jan-07 3:52

9-Jan-07 3:52

Since each creation of a new table adapter will require one or more network round trips to populate the date, it is better to persist a reference as long as the data contained is not likely to become stale rapidly. The trade off is that keeping populated table adapters around can consume a lot of memory if the tables are large.

Adam Crawford9-Jan-07 4:22

Adam Crawford

9-Jan-07 4:22

You seem to be inferring that each instance of a table adapter stores the data from the table in the application. Are you certain this is correct as I assumed that data was only retrieved once the Select function of a table adapter was called in order to fill a data table object? (It is possible that my orginal message was not clear enough and you thought I was referring to data table objects rather than table adapters)
I always assumed that table adapters stored connection details for the appropriate database object - these are the resources I was hoping to find out about so that I can ensure my application is not leaving connections open inappropriately or unnecessarily hammering the database.

Pete O'Hanlon9-Jan-07 4:36

9-Jan-07 4:36

I'm not going to comment on Table Adapters as I don't use (like) them very much. However, you may want to look at using Query Analyzer to see what is happening with your connections (and queries). This tool is invaluable.

Rob Graham9-Jan-07 6:58

9-Jan-07 6:58

NamelessParanoia wrote:
ou thought I was referring to data table objects

Yes, I was confused...
Each call to Myadapter.Fill or Myadapter.Update will cause a network round trip. If you open the connection before calling Fill, then the connection will remain open, otherwise it is opened and closed for you. The data (and schema information) gets stored in the datatable/dataset.

Fine Tuning of Query

Sasmi9-Jan-07 0:56

Sasmi

9-Jan-07 0:56

Hi Guy's
i am searching for the details about 'Fine Tuning of SQL-Query' can any one give me any good site or other details about this. I mean what steps are carried out to increase the performance of SQL Query? so the data will flow fluently please help me...

Thanks in advance

Sasmi

Re: Fine Tuning of Query

Pete O'Hanlon9-Jan-07 1:30