Howdy. I have a table in my DB that has about 2 million records. The search times are taking 15 - 30 seconds depending on the number of records I am returning. Is this normal? The machine is NT 4 sp6a Dual PIII 866's with 1 GB of RAM on RAID5 SCSI disk. This seems like a long time to me. What kind of performance should I expect? Any kind of tuning steps I can take?
Thanks
Shane
Hi,
Some of my queries are running too slow.It's taking as long as 30secs .Earlier the same query was taking less than 5 secs.
I understand the db has grown BUT I do not know to look at this query where should i start from and what should I look into.
It is on production server.
the db size is 15GB and unallocated is 9GB.
log space used is 4%.
TIA.
Hi All,
Am very new to SQL server so don't really understand what effects the speed of queries. I have the two below queries, which are nearly the same apart from one has a right join and the other doesn't. The both return about 5000 records, and I am implementing this query from an accss databse with an odbc link to sql server. What I don't understand is it takes about 8 seconds for the query with the right join in to return the records and only about 4 seconds for the one without. What I'm after really is just some general advice on how to bulid fast queries, and any advice on the two below queries would be nice. Thanks
SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators INNER JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.ContactID)=1442))
ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;
SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators RIGHT JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.ContactID)=1442))
ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;
Hi, I have absolutely no knowledge of PHP or SQL .... I moderate a PHPBB forum at www.savingshelterpets.com
Our web host (SiteGround) has taken our site down temporarily because we are overloading the server. I have no idea how to fix the problem, so hopefully someone here can help me out! Smiley
PHP version 4.4.4
MySQL version 5.0.27-standard-log
Here's the info sent to me by SiteGround (I don't understand a word of it!):
quote:Upon further investigation, it turned out that the following queries in your account are slow and heavily consume server resources:
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 1284
use savingsh_phpbb2;
SELECT user_id, username, user_password, user_active, user_level, user_login_tries, user_last_login_try
FROM phpbb_users
--
delete from rs_stat_ip where platnost_do<'2007-08-03 16:49:43';
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 5 Lock_time: 3 Rows_sent: 1 Rows_examined: 0
use savingsh_phpbb2;
SELECT * FROM phpbb_optimize_db;
# User@Host: binaryte_lhlp1[binaryte_lhlp1] @ localhost []
--
# Time: 070803 16:50:27
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 4 Lock_time: 2 Rows_sent: 1 Rows_examined: 0
use savingsh_phpbb2;
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
FROM phpbb_topics t, phpbb_forums f
In order to have the limitations removed, please optimize your script.
Some queries take a long time to complete.
Setup is:
- SQL Express SP2
- Windows Vista Business
- 2 GB RAM
- Core 2 Duo processor
- Connecting to (local) server with SQL Authentication
- only 1 Instance of MSSQLSERVER
Simple queries (SELECT * FROM TableName) wher the table has only a few records. This query may take up to 30 or more to execute. This slowness is consistent to certain tables. Other much larger tables run queries fine.
If a different computer logs in to the same server, queries provide instantaneous results.
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.
The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:
select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0
As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).
I've been using MS-SQL Server for many years but never come across this problem before.
When I try and run a very simple query from Query Analyzer it takes a LONG time. Even when there are no tables involved!
Even:-
select 1
go
takes 28 seconds to return '1' when running against the local server. i.e. both QA and the Server are running on the same machine.
Can anyone help explain how to get my performance back! Thanks.
Hi,
I have succesfully created a Stored Procedure which runs under 2 seconds locally.
However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.
My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.
Any Ideas why this would be happening?
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
I have a 2GHZ cpu with 1GB of RAM. I occassionally see very slow (long) queries against a local SQL Server 2005 Express (SP2) database. The issue occurs against different SQL Queries, but all queries are rather basic select statements Perfmon shows that the SQL Server counter for the "MEMORY GRANT QUEUE WAIT Avg MS" gets extremely high (25000+ ms). Perfmon also also shows that PAGING is not occuring, and the system is not under unsual stress. The problem is not reproducible with MSDE.
Has anyone seen this issue, or have any recommendations for a next course of action?
I have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.
Issue:
1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedI am writing a data access web page, but I find that the excution speed is too slow.
My data base is just a data table which have five columns: id, code, quantity, price and Date. The data base has about 45000 rows. When I use OSQL or Query function, speed is just fine.
Here is the main code which I think cause the speed slow:
string conn = ConfigurationSettings.AppSettings["connectionstring"];
SqlDataAdapter adapter_2 = new SqlDataAdapter("select * from table",conn);
DataSet ds = new DataSet();
adapter_2.Fill (ds,"table");
DataTable YahooOrders = ds.Tables["YahooOrders"];
DataRow[] product = new DataRow[20000];
.......
foreach (string s in split) // actually the split here has only one string in it
{
product = table.Select ("code like '"+s+"%' and Date >='"+minDate+"' and Date <='"+table.Select("Date = Max(Date)")[0][1].ToString()+"'");
foreach(DataRow myRow in product)
{
int count = Convert.ToInt32(myRow[2]);
itemQuantity = count + itemQuantity;
revenue = Convert.ToDouble(myRow[3]) * count + revenue;
// get product code, ignore repeated code
int myIndex=code.BinarySearch( myRow[1] );
if ( myIndex < 0 )
code.Add(myRow[1]);
}
orderQuantity = product.Length + orderQuantity;
}
The first foreach actually excutes just one time, so it won' t cause any speed problem.
The second foreach' s job is to sum each column of specified rows which is product here.
So, any ideas about this?
Thanks!
All,
Actually from the application the developers are using count(column) to know the no. of rows resulted by a statement which joins many tables but its taking lot of time.
Is there an easy way to get the count of records(result set) of the output.
I cant use sysindexes b'z i need the count of the output genereted by the SQl Statement which joins many tables and retrieves many rows.
Thanks,
Sajai
Hi fellas (and girllas),
Got a problem (duh!). My MSSQL Server lags. Now, mind, it doesn't lag all the time. And it seems to be independent of the # of users trying to access the server. And it random clears itself up. And the problem doesn't present itself in SQL MGR, just on the web app we're running on it.
Setup:
SQL Server 2k running on 2k3 w/ IIS & backup exec.
All SQL data files are on a raid5 SCSI U160.
App:
Intranet App developed by us for us. ASP.NET & VB.NET.
Symptoms:
When queried server takes a LONG time to respond. So long infact it has become counter productive. When taking a look at the server, the CPU usage hovers between 50-75% and spikes up to 90% every now and then just for kicks. The memory usage is 2.35gb out of 4gb. To fix this we have to kill and restart all the SQL services.
Any thoughts on what to look at? There're indexes on the required FKs and the heavily queried columns. We're at a loss here.
Thanks for any helpful help!
=Me!
Database has its few HUGE tables, but I experienced queries against few other very small tables to be incredible slow.
Anybody has the same or similar problem?
A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?
Thanks
Hello,
I have 4000 record in my table employee. it takes 13 sec to get data. It this normal ? What is wrong ?
Thanks
Code Snippet
CREATE FUNCTION [dbo].[VrniStrukturo] (@id_sod int)
RETURNS TABLE
AS
RETURN
(
WITH tree(id, parent_id, naziv, nivo) AS
(
-- Base case
SELECT
id,
parent_id,
naziv,
1 as nivo
FROM employee
WHERE id = @id_sod
UNION ALL
-- Recursive step
SELECT
e.id,
e.parent_id,
e.naziv,
eh.nivo + 1 AS nivo
FROM employee e
INNER JOIN tree eh ON
e.parent_id = eh.id
)
--SELECT *
SELECT id
FROM tree
--ORDER BY nivo, priimek, ime
);
There re certian times when I want to execute a sql request (select for example) then It gets too long before I get an answer. (that happens only some times exceptionnaly). What does that mean, is it that somebody is using heavily the DB or may be using Entreprise manager or what exactly
and how can I know who is responsible for taking all SQL server resources at that specefic time. What command or what tool can I use pls for this purpose.
Thanks for your help.
Hello,Were using the data transfermation service to copy in an Ingres II 2.5database to an SQL Server 2000 database. Small databases don't present anissue, but when pulling one across that's about 20GB its been taking between12 and 24 hours. Both systems are relitively quick boxes and neither of themare tapped out on processor, disk I/O or network resources.I do have the "Boost SQL Server priority on Windows" checked under it'sproperties and all the processors are checked to be used.Does anyone know if there's a way to tweak SQL Server or Ingres to handlethis a little quicker? Or even an idea where the bottleneck could be may behelpful.Thanks,John.
View 1 Replies View RelatedI've got a performance question about a clr tvf that I have created. When I query the function it takes about 30 seconds for it to execute as apposed to < 6 seconds when I execute the same code in a console app (the 6 seconds includes outputting the returned data to the console, without writing the output to console it executes in about 1 second). Both the function and the app are iterating (>40,000) and returning ( >10,000) the same number of rows. I've noticed the following when viewing the executions in the PerfMon:
* the sqlclr tfv kicks the % Processor Time up to 30 for 30 seconds, the console app has % Processor Time at 9 for about 2 seconds
* .NET CLR Memory - Allocated Bytes/sec spikes anywhere from 1 to 3 times during the sqlclr query at about 44MB/sec. It barely registers if at all when the console app runs.
* In either case, % Time in GC is at zero.
I'm assuming that there are some configurations I'm ignorant of that can help me tune the execution. I can't imagine that it takes SqlServer that long just to iterate through the records.
I have recently decided to make the change from Microsoft access to SQL Server believing that it's a bigger faster beast with better parameterized queries and triggers and all that. BUT.I have some client data that I imported from their original paradox files.The invoice lineitem file contains over 1 milliion records.When I open this table in access and click show last record, the record is displayed in about 1 or 2 seconds.I used the upsize to SQL Server tool in Access to shift my data into SQL Server.When I use the Express Mangagement tool to open the same table and say show me the last record, it takes 17 minutes.I admit that most numeric data types have been translated to floats, so that's probably not good.But I cant alter them from floats to numeric or decimals using the table design tool.Do the conversion anomalies make up the whole reason why SQL Server seems so incredibly SLOW! ?????????
View 2 Replies View RelatedI am running the following BCP to extract a table with 156641604 rows.
bcp TestDB..data out test3.bcp -T -b1000000 -a32000
When running this i notice that the disk read bytessec counter in performance monitor on the drive that has the database devices is only reading 30mbsec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?
Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again.
The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs.
I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines ..... all with different sites, the only thing in common between them is SQL.
thanks,
-c
This sounds like a pretty easy one. I have a SQL 2000 database with 2-3.4GHZ CPUs and 1GB of RAM. I have one database on it. I go in Query Analyzer on another machine and run a simple query like 'SELECT * FROM USERS' which should return 15,000 rows.
IT takes 30 (thirty) seconds to finish this query. OMG
Where do I start to decipher why on Earth this takes more than .01 seconds?
Thanks.
Hi all...
I need urgent help, about someting:
i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
please help me what should i do......
Dear All,Finally I completed my project. Thanks all you helped me to do it.Now I have the biggest problem. In my application the Data grid is filled with Data from SQL server table which has large number of records. When I run my queries in SS Management Studio it runs very fast. To fill data to datagrid it takes lots of time. How can I reduce this time. How can I increase the performance of my Application.Thanks,Janaka
View 4 Replies View RelatedHello,
We have Sql Server installed on the Windows 2000 machine.
There are 2 databases that the employees access on it.
When the machine is just started, there are no problems but after some time the connections get really really slow and then eventually become impossible to connect. We did not have this problem before until we had a computer crash and then Sql server was installed on a new machine. The problems started here. We had 64mb of ram and we put in another 64mb thinking this would solve it but it did not.
On the task manager the cpu percentage stays constant at 100% and the virtual memory increases and increases very slowly until it has reached it's maximum (which is 600mb).
Any information would be greatly appreciated.
Thanks very much,
Kostas
Hi,
I have a query which has suddenly started responding slow.
CAn anyone tell me what could be the possibilities?
I tried update stats(I am on sql 70-though it's done auto but i did it manually again)
I used union all in place of union but had no big effect.any othe thought?
Thanks!
After I installed SP3 on a production SQL box, backups are a lot slower. Anybody have any suggestions?
Thanks in advance
Joe
I am configuring a new Server running SQL Server 7.0 sp2.
I have a job that runs a large process. From the SQL Agent job, the processing time is over 8 hours. If I run the same process from a query window, it takes an hour and a half. On my 6.5 database, the job takes 2 - 3 hours from the scheduled task. What is going on with SQL Agent? I have not been able to find any information on memory and SQL Agent.
Any help would be greatly appreciated!
Thanks
Trina Blazek