|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
can VC++ work with microsoft access 2000?
|
|
|
|
|
|
hai friends how to add the pictures and movie in sql.
ganesh
|
|
|
|
|
Store it in an image column (SQL Server 2000) or a varbinary(max) column (SQL Server 2005)
|
|
|
|
|
|
hi friends
what is the cursor? in sqlserver2000.plz explain
regards
saravanan
|
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Hi Guys..
Can anyone tell me why this DTS Package is used for, and how to implement it..???
The name is Sandeep
|
|
|
|
|
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
|
|
|
|
|
You have to use either Parameters in the update command.
But the best practice is to pass the users input
to a "STORED PROCEDURE" and let the SP do the job.
Regards,
Arun Kumar.A
|
|
|
|
|
The problem is that ' ends your quotes for your string value. In fact, if you typed this "value' GO drop database mydatabase GO", you'd find that the SQL after the ' GO is going to execute. You need to read the articles on SQL injection attacks that exist here on CP.
The short answer is to replace ' with ''. But, please read the article, your code is not safe.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Can you recommend a good one?
/\ |_ E X E GG
|
|
|
|
|
This[^] is the best article for it on CP.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello,
VS 2005
I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.
This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)
However, I have to change this column to a DateTime without destroying the data.
Is there any easy way to write some script or use studio management to change this.
Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.
The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.
I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Any suggestions would be most grateful,
Thanks,
Steve
|
|
|
|
|
It's most likely that the data is being interpreted in US English (MM/dd/yyyy) format.
You could try using a query window (right-click the database and select New Query) and using the following
SET LANGUAGE British
ALTER TABLE table
ALTER COLUMN column datetime where table is the name of the table and column the name of the column to alter.
If that doesn't work, try adding a new column of type datetime and then using an UPDATE statement with SET newcolumn = CONVERT( datetime, oldcolumn, 103 ) to do the conversion, then drop the old column.
|
|
|
|