Re: Veeeeeeeeeery slow queries in MS SQL 2005 - Database Discussion Boards

Re: MSDE Problems

Paul Conrad4-Jun-07 7:06

Paul Conrad

4-Jun-07 7:06

ma se wrote:
Is there a future for MSDE?? Is there a version that comes with SQL Server 2005?

It is SQL 2005 Express.

ma se wrote:
Can I use SQL Server 2000 and MSDE and SQL Server 2005?

I think as long as the instance names are different, you can.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

checking the datatype of a column in a table

steve_rm4-Jun-07 6:00

steve_rm

4-Jun-07 6:00

Hello,

I have the following sql statement

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)<br />
<br />
    ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

Re: checking the datatype of a column in a table

Arun.Immanuel4-Jun-07 6:53

Arun.Immanuel

4-Jun-07 6:53

It seems like the the converted values will be in the format "yyyy/mm/dd"
and those which have to be converted will be in the format "dd/mm/yyyy".

So check the records which has 3rd character '/' and then covert those values alone.

In the update clause , include a where condition as follows:

WHERE SUBSTRING([Date],3,1)='/'

Hope that helps you.

Regards,
Arun Kumar.A

Re: checking the datatype of a column in a table

andyharman4-Jun-07 6:58

andyharman

4-Jun-07 6:58

For SQl-Server 2000, the following should work:

if exists(select sc.*
        from sysusers su
        inner join sysobjects so
        on so.uid = su.uid
        inner join syscolumns sc
        on sc.id = so.id
        where su.name = 'dbo'
        and so.name = 'OtherCall'
        and so.xtype = 'U'
        and sc.name 'Date'
        and sc.usertype = 2)
begin
    --your update
    --your alter-table
end

It should also work with SQL-Server 2005, but you ought to use the new system-catalog views.

Regards
Andy

Veeeeeeeeeery slow queries in MS SQL 2005

3fonov4-Jun-07 3:51

3fonov

4-Jun-07 3:51

Hello all!
I've got some problem. When i'm executing about 10 stored procedures it's tooks 5 second to complete.
Here is procedures code:

ALTER PROCEDURE [dbo].[update_source]<br />
-- Add the parameters for the stored procedure here<br />
@internal_id int,<br />
@parent_id int,<br />
@sstate tinyint,<br />
@title nvarchar(50),<br />
@rng decimal(4,2),<br />
@lat dbo.latitude,<br />
@lon dbo.longtitude,<br />
@course dbo.angle,<br />
@speed dbo.speed,<br />
@stype xml<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
DECLARE @source_id int;<br />
DECLARE @state int;<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
IF @source_id IS NULL<br />
BEGIN<br />
INSERT INTO sources<br />
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)<br />
VALUES<br />
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);<br />
<br />
SET @source_id = (SELECT source_id FROM sources WHERE internal_id = @internal_id);<br />
END<br />
ELSE<br />
SET @state = (SELECT sstate FROM sources WHERE source_id = @source_id);<br />
UPDATE sources SET<br />
parent_id = @parent_id,<br />
updated = getutcdate(),<br />
sstate = @sstate,<br />
title = @title,<br />
rng = @rng,<br />
course = @course,<br />
speed = @speed,<br />
stype = @stype,<br />
lat = @lat*100000000,<br />
lon = @lon*10000000<br />
WHERE source_id = @source_id;<br />
<br />
<br />
<br />
INSERT INTO source_states<br />
(source_id, sstate, acquired)<br />
VALUES<br />
(@source_id, @sstate, getutcdate());<br />
<br />
END

and i'm execute it like this:

<br />
EXEC update_source 2190074, 5, 1, N'2190074', 0,57.7386783333333,10.57468,0,0,N'';<br />
EXEC update_source 2573335, 5, 1, N'2573335', 0,63.8,9.73333333333333,0,0,N'';<br />
.....<br />
.....<br />
.....<br />
EXEC update_source 2573105, 5, 1, N'2573105', 0,59.6166666666667,10.5166666666667,0,0,N'';<br />
EXEC update_source 2573235, 5, 1, N'2573235', 0,60.4,5.35,0,0,N'';

I think that mistake not in my code but somewhere in server configuration. But i can't find it out.
Thanks a lot!

Re: Veeeeeeeeeery slow queries in MS SQL 2005

andyharman4-Jun-07 6:13

andyharman

4-Jun-07 6:13

Which columns have indexes or primary keys specified? I would expect a clustered primary-key on the "internal_id" column and a unique index (or unique constraint) on the "source_id" column.

Regards
Andy

Re: Veeeeeeeeeery slow queries in MS SQL 2005

3fonov4-Jun-07 7:01

3fonov

4-Jun-07 7:01

Yes it is. And it doesn't depends on table size or data size.
When it's executed in Managment Studio with Client statistics everything is zero.
Thank you for quick reply

Re: Veeeeeeeeeery slow queries in MS SQL 2005

andyharman4-Jun-07 7:28

andyharman

4-Jun-07 7:28

If you run your 10 stored procedure calls from a Management Studio query then how long does it take?

If it takes a long time then I would be looking at a configuration issue (you should be able to comfortably hit between 30-50 calls per second).
o What other indexes do you have on that table?
o Do you have any triggers?
o What else is happening on the database server?
o Are you data files held in a compressed folder?
o Is the transaction log being written to a separate disk?

If it is really quick through the SQL-Server tools then I would be looking at your front-end code.
o Are you using the native SqlClient connection?
o Are you reusing your dbcommand object (using parameters).
o Are you opening a separate connection for each call?
o Is the database on the same server? Do you have network problems?

Hope that helps.
Andy

Re: Veeeeeeeeeery slow queries in MS SQL 2005

3fonov4-Jun-07 22:52

3fonov

4-Jun-07 22:52

Thanks for help but...
I try to run this on clean installation on empty tables and got this in Profiler: Print Screen.
There is no triggers, compressed folders, and so on.
Theres is selected a bit more complicated procedure, but as i think it must not take 3 seconds to run got it indexes or not.
The same routines on MySQL server takes zero time and CPU perfomance.
Thanks for advises.

Re: Veeeeeeeeeery slow queries in MS SQL 2005

Pete O'Hanlon4-Jun-07 23:30

Pete O'Hanlon

4-Jun-07 23:30

First of all, is source_id an autoincrement column? If it is, don't reselect it - use the Scope_Identity() function to retrieve it in your insert, e.g.

INSERT INTO sources
(internal_id, parent_id, updated, sstate, title, rng, course, speed, stype,lat,lon)
VALUES
(@internal_id, @parent_id, getutcdate(), @sstate, @title, @rng, @course, @speed, @stype, @lat*100000000, @lon*10000000);
SET @source_id = SCOPE_IDENTITY()

If it's a uniqueidentifier column, select the GUID yourself and then insert it as part of the insert statement.

Secondly, why are you performing the SELECT @state = select in the update portion? I can't see anywhere that you are using this in your procedure.

Third, you are performing a calculation in your query. I would consider either moving it outside the query, or using a computed column in the table.

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

Re: Veeeeeeeeeery slow queries in MS SQL 2005

3fonov4-Jun-07 23:51

3fonov

4-Jun-07 23:51

I move this database to MS SQL Express and it's start working 30 times faster as i assumed it must.
So problem somewhere in server configuration not database design.

Thanks

microsoft access 2000

villaa4-Jun-07 3:05

villaa

4-Jun-07 3:05

can VC++ work with microsoft access 2000?

Re: microsoft access 2000

Rob Graham4-Jun-07 5:03

Rob Graham

4-Jun-07 5:03

Yes. See Carlos Antollini article here[^] for ADO interface classes, and here for connection strings[^] (Look for Jet OLEDB Provider).

how to add the picture or movie into the table

ganesamoorthidhayalan4-Jun-07 2:15

ganesamoorthidhayalan

4-Jun-07 2:15

hai friends how to add the pictures and movie in sql.

ganesh

Re: how to add the picture or movie into the table

Colin Angus Mackay4-Jun-07 2:39

Colin Angus Mackay

4-Jun-07 2:39

Store it in an image column (SQL Server 2000) or a varbinary(max) column (SQL Server 2005)

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: how to add the picture or movie into the table

Giorgi Dalakishvili4-Jun-07 5:41

Giorgi Dalakishvili

4-Jun-07 5:41

have a look at this: support.microsoft.com/kb/309158

my articles

what is the cursor?

saravanan053-Jun-07 23:13

saravanan05

3-Jun-07 23:13

hi friends

what is the cursor? in sqlserver2000.plz explain

regards
saravanan

Re: what is the cursor?

originSH3-Jun-07 23:25

originSH

3-Jun-07 23:25

http://www.google.co.uk/search?hl=en&q=sql+server+2000+cursors&meta=[^]

Re: what is the cursor?

ganesamoorthidhayalan4-Jun-07 2:20

ganesamoorthidhayalan

4-Jun-07 2:20

cursor means to select the set of record, and mainpulate that record and stored in againg to the database.

for example

declare cursor s1 as select * from emp where salary>1000;

if s1 is the cursor name. The cursor having the records in salary is greater than 1000. we can trace each record and to manipulate the record and to again store to database.

Homework alert

leckey4-Jun-07 4:06

leckey

4-Jun-07 4:06

Based on your last several posts it looks like you are wanting people to answer your homework questions. Use Google for your broad questions.

__________________
Bob is my homeboy.

Re: Homework alert

originSH4-Jun-07 5:19

originSH

4-Jun-07 5:19

This is why most of my answers involve simply a google search lol, unless a question is specific and indicates the author has atleast looked for information and a soltuon of course Smile | :)

Re: Homework alert

leckey4-Jun-07 9:55

leckey

4-Jun-07 9:55

I think that's fine for maybe the first or second post, but I think this user has been warned before.

__________________
Bob is my homeboy.

SQL 2000 Reporting Services - Blank pages while rendering matrix in PDF format

Ravindranath Kini3-Jun-07 19:07

Ravindranath Kini

3-Jun-07 19:07

I am using a matrix control in a report containing 2 subreports. The matrix control is present in one of the subreports. When I export the full report into the PDF,if the matrix are rendered in a single page, the report is fine and there are no blank pages. But when the number of columns exceeds the first page and moves into the second page then there are 3 blank pages in the report for every page with content. The number of blank pages after each page with content is equal to the number of times the matrix would scroll into a new page.

This problem occurs only when I try to generate the whole report in PDF. No blank pages appear when I render subreports into PDFs individually.

I am using SQL Reporting Services 2000 SP1.

Thanks in advance.

-- Ravi

DTS Package..??

Xandip3-Jun-07 17:23

Xandip

3-Jun-07 17:23

Hi Guys..

Can anyone tell me why this DTS Package is used for, and how to implement it..???

The name is Sandeep

Basic SQL question

eggie53-Jun-07 14:42

eggie5

3-Jun-07 14:42

I have some code (C#) that runs an SQL update query that sets the value of a column to what the user passes. So, this causes an error when anything the user passes in has a ' character in it. I'm sure there's other characters that'll break it too. So, I was wondering, how do I get around this? Is there some commonly accepted regex pattern that will make the value safe to run in an SQL query? How can I take care of any values that need to be escaped?

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;


        try
        {
            int result = command.ExecuteNonQuery();

            if (result != 1)
            {
                Response.StatusCode = 500;
                Response.Write("The file has been uploaded, but we could not update the DB");
                Response.End();
            }
        }
        catch (InvalidOperationException)
        {
            Response.Clear();
            Response.Write("error");
            Response.StatusCode = 500;
            Response.End();
        }

        connection.Close();

/\ |_ E X E GG

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