sql bit data type: statements

Showing posts with label statements. Show all posts

Thursday, March 22, 2012

Book recommendation on performance

I have the Transact-SQL Programming book from O'Reilly. It was
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.

Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?php newbie (newtophp2000@.yahoo.com) writes:
> I have the Transact-SQL Programming book from O'Reilly. It was
> published in 1999. It states that "SELECT ... INTO" statements end up
> locking the entire database of the target table. Since the tempdb is
> also involved (in many cases), this creates major deadlocks for the
> entire database and all users. It suggests using the "INSERT ...
> SELECT" form instead.
> Considering that the book is somehow dated, is this recommendation
> still valid, especially on target sizes of up to 5 million records?

To a large extent, no. The author seems to have had SQL 6.5 in mind, where
SELECT INTO a temptable, indeed to bring a server to a stand still. The
problem is that SELECT INTO creates the temp table, and then goes on to
fill it with data. Since the query is one transaction, it keeps a lock on
the system tables until the query has completed. This was fatal in SQL 6.5
which only had page locks. In SQL7 and SQL2000 where you have row locks,
the impact on other processes is much smaller.

The advantage of SELECT INTO is that is that is minimally logged, so
SELECT INTO #tmp for five million rows can be faster and take less
toll on the server than CREATE TABLE INSERT INTO.

Nevertheless, there are contexts where SELECT INTO is an inferior choice
over CREATE TABLE not talking about a table variable. Say that you already
have a transaction in progress, and you creating many small tables
repeatedly within this transaction. SELECT INTO takes out more locks on
than CREATE TABLE, so you acquire a whole lot more locks with SELECT
INTO, and this can have an impact on performance. Case in point: I had
a procedure which suddently started to perform much slower than before.
This procedure performs some complex data updating in an iterative
fashion. My profiling pointed to a seeminginly innocent query which
appeared to take longer and longer time as the procedure proceeded.
Eventually I found the answer in a trigger (which was not affected by
this query). I had replaced direct deferences to "inserted" with temp
table created through "SELECT * INTO #inserted FROM inserted".

To summarize: SELECT INTO is fine for single-time queries on large
tables. It is bad to have in triggers in tables which maninly are
updated one row at a time.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 8, 2012

Blocking and return parameters

Is it possible the existence of a return parameter on an SP running
insert statements would cause blocking to happen (any removing it
might help eliminate it?)
The SPs in question have three INSERT INTO statements using an
@.@.IDENTITY to help populate the second and third tables based on the
PK of the newly inserted record in the first INSERT INTO Statement.
I have recently stared to receive timeout errors in a multi-user
enviroment and wonder if that's part of the cause. In this
environment, users are requerying a table that is also being written
to. The writes are infrequent but the reads of that table are very
frequent.
On the read side I have added Select from TableX WITH (READPAST)...
On the write side I have elimnated the return parameters. I'm hoping
these two approaches help.
Help is greatly appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

As a general comment, if your three INSERTs are inside a transaction, and
that transaction runs for too long, or requires a lot of locks, then it's
possible that the readers will be affected. But without some more
information, such as the piece of code containing the INSERTs, and some
background information about the tables, it's hard to say.

By the way, READPAST is potentially dangerous, because it means the client
will not see data which it should normally see, so the results returned may
be incomplete. Locking hints are not often required, so you should try other
solutions first.

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> Is it possible the existence of a return parameter on an SP running
> insert statements would cause blocking to happen (any removing it
> might help eliminate it?)
> The SPs in question have three INSERT INTO statements using an
> @.@.IDENTITY to help populate the second and third tables based on the
> PK of the newly inserted record in the first INSERT INTO Statement.
> I have recently stared to receive timeout errors in a multi-user
> enviroment and wonder if that's part of the cause. In this
> environment, users are requerying a table that is also being written
> to. The writes are infrequent but the reads of that table are very
> frequent.
> On the read side I have added Select from TableX WITH (READPAST)...
> On the write side I have elimnated the return parameters. I'm hoping
> these two approaches help.
> Help is greatly appreciated.
> lq

I'm not entirely sure I follow your description - stored procedures can have
return values, and output parameters, but not "return parameters" so I'm not
clear as to which one you mean here.

Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

Alter Procedure "mySPName"
@.par1 nvarchar(15) = null,
@.par2 int = null,
@.par3 nvarchar(50) = null,
@.par4 nvarchar(50) = null,
@.par5 nvarchar(125) = null,
@.par6 smallint = null,
@.par7 ntext = null,
@.par8 smalldatetime = null,
@.par9 smalldatetime = null,
@.par10 smalldatetime = null,
@.par11 smalldatetime = null,
@.par12 datetime = null,
@.par13 datetime = null,
@.par14 smallint = null,
@.par15 smalldatetime = null,
@.par16 tinyint = null,
@.par17 tinyint = null,
@.par18 tinyint = null,
@.par19 nvarchar(100) = null,
@.par20 int = null,
@.par21 int = null,
@.par22 int = null,
@.PKID int OUTPUT

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

INSERT INTO
tblAppointments (fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8,
fld9, fld10, fld11, fld12, fld13, fld14, fld15, fld16, fld17, fld18,
fld19, fld20, fld21,fld22,fld23, fld24)
VALUES
(@.par1, @.par2, @.par3, @.par4, @.par5, @.par6, @.par7,
@.par8, @.par9, @.par10, @.par12,@.par13, @.par14, @.par15, @.par16, @.par17,
@.par18,
@.par19, @.par20, @.par21,@.par22, @.par22,@.par18, @.par18)

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

Insert Into
tblAppointmentsHistory (fldA, fldB, fldC, fldD, fldE)
Values
(@.PKID, @.par2, @.par3,'New Entry','Appt Type = ' + @.par4 + ' ;
AppTitle= ' + @.par5 + ' ; AppDescription= ' + isnull(Cast(@.par7 AS
nvarchar(4000)),'<none>') + ' ; Location = ' + @.par19 + ' ; Start
Date = ' + CAST(@.par12 AS nvarchar(25)) + ' ; End Date = ' +
IsNull(CAST(@.par13 AS nvarchar(25)),'<none'))

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||Simon,
Thank you for your response. I meant to write that in VBA I have
appended an Output Parameters to the prm collection.

Here is the entire SP:

*** START SP CODE:

AS
set nocount on
set xact_abort on

/* Insert new event into tblAppointments */

/* Note: Some columns above receive data from the same parameters */

SELECT @.PKID = @.@.IDENTITY

/* Create an entry in tblAppointmentHistory */

***END SP CODE

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<407af642$1_2@.news.bluewin.ch>...
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0404110746.4de8d6d7@.posting.google.c om...
> > Is it possible the existence of a return parameter on an SP running
> > insert statements would cause blocking to happen (any removing it
> > might help eliminate it?)
> > The SPs in question have three INSERT INTO statements using an
> > @.@.IDENTITY to help populate the second and third tables based on the
> > PK of the newly inserted record in the first INSERT INTO Statement.
> > I have recently stared to receive timeout errors in a multi-user
> > enviroment and wonder if that's part of the cause. In this
> > environment, users are requerying a table that is also being written
> > to. The writes are infrequent but the reads of that table are very
> > frequent.
> > On the read side I have added Select from TableX WITH (READPAST)...
> > On the write side I have elimnated the return parameters. I'm hoping
> > these two approaches help.
> > Help is greatly appreciated.
> > lq
> I'm not entirely sure I follow your description - stored procedures can have
> return values, and output parameters, but not "return parameters" so I'm not
> clear as to which one you mean here.
> As a general comment, if your three INSERTs are inside a transaction, and
> that transaction runs for too long, or requires a lot of locks, then it's
> possible that the readers will be affected. But without some more
> information, such as the piece of code containing the INSERTs, and some
> background information about the tables, it's hard to say.
> By the way, READPAST is potentially dangerous, because it means the client
> will not see data which it should normally see, so the results returned may
> be incomplete. Locking hints are not often required, so you should try other
> solutions first.
> Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

<snip
At first glance, there doesn't seem to be anything obviously incorrect with
your procedure, assuming that you haven't removed anything significant from
it. Small issues are that you should probably use SCOPE_IDENTITY() instead
of @.@.IDENTITY (see Books Online), and you may want to review your use of SET
XACT_ABORT:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

To go back to your original timeout issue, why do you think the timeouts are
connected to this specific procedure? Have you monitored what's happening on
the server when timeouts occur, especially locking? You can use Profiler to
capture what's happening on the server, and this KB article may be useful:

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404121731.52fe5214@.posting.google.c om...
> Simon,
> Thank you for your response. I meant to write that in VBA I have
> appended an Output Parameters to the prm collection.
> Here is the entire SP:

http://www.sommarskog.se/error-hand...html#XACT_ABORT

http://support.microsoft.com/defaul...9&Product=sql2k

Erland also has a tool for examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon

Saturday, February 25, 2012

BLOB in T-SQL

Hi,

can anyone help ,me out here with some design consideration reguarding importing of BLOB data to a SQL server 2000 using T-SQL statements?

I want to make an import of some documents which are stored in a Access database, to an Ms SQL server 2000. The documents are stored in the access database as a OLE Object, by now I thought of using the base64String function to convert the data from the access field and write it to the T-SQL statements which will written in a text batch file. And then I apply the SQL Convert function something like:

INSERT INTO testBin VALUES(convert(image,'base64sting_encoded'))
go

Does this work? Is it correct what I am doing?

Thanks.

one rule i remember about handling blob

is that it needs to be on a separate filegroup to avoid

fragmentation

|||

Access OLE Object column data goes directly into image in SQL Server. So I am not sure why you are doing the convert and it may not really produce the correct results. See below links for more details on Access migration:

http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

http://www.microsoft.com/sql/solutions/migration/default.mspx

See the link below for some great information on when to store BLOBs in the database:

http://research.microsoft.com/research/pubs/view.aspx?type=technical+report&id=1089

|||that was also what i have in mind though i hesitate to say

Friday, February 24, 2012

Blank vs. Space

Hello, it seems to me that T-SQL is having a hard time
distinguishing a blank from one or more spaces. When I run
the statements below, the SELECTs always give 1
as a result. This seems completely wrong to me.. or am I
missing something?
CREATE TABLE TempTable (Value VARCHAR(10))
INSERT INTO TempTable (Value) VALUES ('')
SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
using Query Analyzer on SQL Server 2000 sp3a
Thanks,
SteveSQL Server does not take in mind spaces in the right side when using operato
r
= to do the comparison.
Example:
select 1 where space(0) = space(10)
go
try:
SELECT COUNT(*) FROM TempTable WHERE datalenght(Value) = 0
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
go
AMB
"Steve Deering" wrote:

> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||This is, I believe, according to the ANSI SQL definition. You can use LIKE i
nstead, as LIKE is
sensitive to trailing spaces:
SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Trailing spaces are dropped so this is working as advertised.
Try this and you'll see what I mean.
declare @.value varchar(10)
set @.value = ' '
SELECT len(@.value)
...returns 0
This:
set @.value = ' b '
SELECT len(@.value)
...returns 2, the leading space and 'b'
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Tibor,
I think it is right except for the first one.
Example:
select 1
from
(
select space(0) union all select space(1) union all select space(2)
) as t(colA)
where colA like space(0);
AMB
"Tibor Karaszi" wrote:

> This is, I believe, according to the ANSI SQL definition. You can use LIKE
instead, as LIKE is
> sensitive to trailing spaces:
>
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Deering" <SteveREMOVE@.vin.com> wrote in message
> news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
>
>|||Indeed. Thanks Alejandro... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CCBDF7B5-B5B2-459D-9DA8-339EFC53AA59@.microsoft.com...
> Tibor,
> I think it is right except for the first one.
> Example:
> select 1
> from
> (
> select space(0) union all select space(1) union all select space(2)
> ) as t(colA)
> where colA like space(0);
>
> AMB
> "Tibor Karaszi" wrote:
>|||Huh. Apparently, it's similar for JOINs, as if the values get RTRIM'd:
USE Northwind
GO
CREATE TABLE TempTable (Value VARCHAR(10))
GO
INSERT INTO TempTable (Value) VALUES ('abc') -- Insert a ZLS
INSERT INTO TempTable (Value) VALUES ('abc ')
INSERT INTO TempTable (Value) VALUES ('abc ')
GO
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' -- ZLS
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 1 space
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 2 spaces
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' + Space(5) -- 5 spaces
GO
SELECT * FROM TempTable t inner join
(select value from TempTable where Value='abc ') t1 on t.value = t1.value
WHERE t.Value = 'abc '
DROP TABLE TempTable
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
One of the other code monkeys here posed an interesting question, though we
can't devise an example: What if in some implementation, the trailing
spaces have significant meaning, so that "bob" should not match "bob "?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||My name isn't "bob", it's "bob ".
Do you think that you can legally change your name to add a few trailing
spaces. :-)
When is a door not a door?
When it's a "door ".
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:euQJpVGQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> One of the other code monkeys here posed an interesting question, though
> we
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob
> "?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||You can use LIKE operator in a join condition.
Example:
select
*
from
(
select 'Microsoft'
) as t1(colA)
inner join
(
select 'Microsoft' + space(1)
) as t2(colA)
on t1.colA like t2.colA
go
AMB
"Mike Labosh" wrote:

> One of the other code monkeys here posed an interesting question, though w
e
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob "
?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>

Thursday, February 16, 2012

BLANK Lines

Hi,
I am running a stored procedure with many select
statements in it. I want the output from the stored
procedure NOT to produce Blank lines between each select
query. How can I do this. I am using Query Analyser to
execute the stored procedure and saving the results to a
text file.
Thanks,
SerghiosHi
It is not clear what you are trying to do here. Output in Query analyser is
not really designed for reports. You may want to look at a different tool.
If you are using something like ADO then it is not the blank lines that are
a problem! You will need to move to the next record set to obtain the second
set of results.
Alternatively... if the outputs are the same then you could use a union.
John
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:37aa01c37494$4b8b2d60$a401280a@.phx.gbl...
> Hi,
> I am running a stored procedure with many select
> statements in it. I want the output from the stored
> procedure NOT to produce Blank lines between each select
> query. How can I do this. I am using Query Analyser to
> execute the stored procedure and saving the results to a
> text file.
> Thanks,
> Serghios