Need Help Optimizing Slow SQL Queries (?)
Aug 6, 2007
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.
View 3 Replies
ADVERTISEMENT
Sep 5, 2007
Hi
We are using SQL SERVER 2005, enterprise/standard edition for an application that generates queries at runtime and normally having many joins in it. But these queries are taking lot of time when they are executed. After setting the database parameter PARAMETERIZATION to FORCED, performance of the queries have improved a lot but still we want to improve them further. Is there any other parameter that we can set for improving the performance of the queries. We have created the indexes on the basic columns that will be used while querying but it may happen that some other columns may also be used for searching in which case queries become quiet slow. Is there anything like skip-scan indexes(of oracle) in sql server 2005.
Regards
Salil
View 4 Replies
View Related
Apr 22, 2006
I am having one querry regarding the same line.
In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp.
Please help me out of this problem.
View 1 Replies
View Related
Mar 15, 2001
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.
View 1 Replies
View Related
Oct 10, 2002
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
View 8 Replies
View Related
Jan 5, 2005
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;
View 6 Replies
View Related
Jul 17, 2007
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.
View 4 Replies
View Related
Feb 27, 2008
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
View 6 Replies
View Related
Jul 26, 2007
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).
View 6 Replies
View Related
Oct 12, 2005
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.
View 1 Replies
View Related
May 30, 2007
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?
View 6 Replies
View Related
Apr 11, 2006
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
View 11 Replies
View Related
May 31, 2007
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
View 2 Replies
View Related
Sep 10, 2007
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?
View 1 Replies
View Related
Jul 7, 2014
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.
View 9 Replies
View Related
Sep 4, 2001
I have indexed my SQL Server tables to gain some speed on calling up tables and queries ( using VB and ADO ). It is still very slow...Is there a move I have to make once my tables are indexed or is there any tricks to improve the speed cause I am getting kinda desparate right now :(
View 1 Replies
View Related
Jul 15, 2000
Hi all,
I have a question in regards to optimistic locking:
I have a database conversion that will be running on a SQL 7.0 system. The process needs to be completed ASAP and to this end, I have tried to set up all aspects of the server to be geared towards speed rather than redundancy for the duration of the process (i.e. moving heavily used tables to separate filegroups on a RAID 0 set, dedicating a separate disk for the database log). I was now looking at trying to tweak locking behaviour to enhance performance (as for the duration of the conversion, no other user will be connecting to the database - the only initator of data changes will be the conversion application, which feeds statements serially to the server). As far as I know changing lock settings is something that would be initiated by the application itself, but is there any property I can set on the server to further enhance performance in this area?
Thanks
A
View 1 Replies
View Related
Jun 5, 2002
We are evaluating a tool by Lechotech that can optimize sql statements. It is a pretty good tool, but we would like to compare it against some others. Has anyone seen any other such tools?
View 1 Replies
View Related
Jan 14, 2005
I'm no SQL wiz, just know basics to get me by ... What I'm trying to do is: everytime a record is inserted into an online orders table, that record needs to be inserted into another table in another database, but with added information.
This is the Trigger I came up with:
CREATE TRIGGER OtherDatabaseInsertTrigger
ON dbo.t_order
FOR INSERT AS
DECLARE @CLIENT VARCHAR(30)
DECLARE @OrderNumberID INT
SET @CLIENT = 'DevShed'
INSERT INTO test2.dbo.t_order (order_num,customer_num,
order_date,drop_date,package_id,us_customer,us_system,non_us_customer,
non_us_system,postage_selection,num_pages,num_sides,permit_number,
permit_city,permit_state,permit_zip,bre_company,bre_name_dept,bre_address1,
bre_address2,bre_city,bre_state,bre_zip,brc_company,brc_name_dept,brc_address1,
brc_address2,brc_city,brc_state,brc_zip,rae_company,rae_name_dept,rae_address1,
rae_address2,rae_city,rae_state,rae_zip,return_company,return_name_dept,
return_address1,return_address2,return_city,return_state,return_zip,salutation_other,
printcost,stock,production,personalization,postage_total,listcost,listcharged,
tax_total,order_total,discount,coupon,milestone_1,milestone_2,milestone_3,milestone_4,
milestone_5,milestone_6,has_logo,has_list,has_rad,tag,nonprofit_fee,order_problem,
experian_only,experian_orderid,experian_price,experian_returncode,experian_returnmessage,
experian_queryid,experian_amt_to_purchase,bp_bankname,bp_address1,bp_address2,bp_city,
bp_state,bp_zip,bp_country,bp_phone,bp_signername,bp_signertitle,bp_signeremail,logo_option,
logo_text,signature_option,list_option,layout_id,cd_only,cd_oi,cd_rc,cd_ot,cd_email,
shipping_id,shipping_charge,pfp_pnref,pfp_result,pfp_respmsg,pfp_authcode,pfp_avsaddr,
pfp_avszip,creditcard_num,ccexp_month,ccexp_year,cc_name_on_card,cust_postage)
SELECT order_num,customer_num,
order_date,drop_date,package_id,us_customer,us_system,non_us_customer,
non_us_system,postage_selection,num_pages,num_sides,permit_number,
permit_city,permit_state,permit_zip,bre_company,bre_name_dept,bre_address1,
bre_address2,bre_city,bre_state,bre_zip,brc_company,brc_name_dept,brc_address1,
brc_address2,brc_city,brc_state,brc_zip,rae_company,rae_name_dept,rae_address1,
rae_address2,rae_city,rae_state,rae_zip,return_company,return_name_dept,
return_address1,return_address2,return_city,return_state,return_zip,salutation_other,
printcost,stock,production,personalization,postage_total,listcost,listcharged,
tax_total,order_total,discount,coupon,milestone_1,milestone_2,milestone_3,milestone_4,
milestone_5,milestone_6,has_logo,has_list,has_rad,tag,nonprofit_fee,order_problem,
experian_only,experian_orderid,experian_price,experian_returncode,experian_returnmessage,
experian_queryid,experian_amt_to_purchase,bp_bankname,bp_address1,bp_address2,bp_city,
bp_state,bp_zip,bp_country,bp_phone,bp_signername,bp_signertitle,bp_signeremail,logo_option,
logo_text,signature_option,list_option,layout_id,cd_only,cd_oi,cd_rc,cd_ot,cd_email,
shipping_id,shipping_charge,pfp_pnref,pfp_result,pfp_respmsg,pfp_authcode,pfp_avsaddr,
pfp_avszip,creditcard_num,ccexp_month,ccexp_year,cc_name_on_card,cust_postage
FROM inserted;
SET @OrderNumberID = (SELECT @@IDENTITY)
UPDATE test2.dbo.t_order SET client = @CLIENT WHERE oid = @OrderNumberID;
I don't know if its possible to do an INSERT INTO SELECT with additional fields in the 2nd table, I was trying, but failed. Had to resort to the bottom piece of SQL to get the ID and run a separate query to add the additional items to the new record in table 2.
Any SQL masters out there that can help me make this better, or know of some other way to do this.
Thanks in advance!
View 6 Replies
View Related
Jun 3, 2004
Hello,
I am hoping someone here can help me optimize the following query:
SELECT
INCOMING.DATE_TIME, INCOMING.URL, INCOMING.HITS,
USER_NAMES.USER_LOGIN_NAME,
CATEGORY.NAME
FROM
(wsHQMay2004.dbo.INCOMING INCOMING INNER JOIN wsHQMay2004.dbo.CATEGORY CATEGORY ON INCOMING.CATEGORY = CATEGORY.CATEGORY)
INNER JOIN wsHQMay2004.dbo.USER_NAMES USER_NAMES ON INCOMING.USER_ID = USER_NAMES.USER_ID
WHERE
INCOMING.DATE_TIME >= '2004-05-01 00:00:00.00' AND
INCOMING.DATE_TIME < '2004-06-01 00:00:00.00'
ORDER BY
INCOMING.URL ASC
I am just hoping to get some tips on perhaps a better way to write this query as right now, due to the size of the incoming table, this query just takes forever.
Any advise will be apreciated.
Thanks.
View 5 Replies
View Related
Jul 7, 2004
I've tried a bunch of different ways in an effort to stay away from using a cursor, but I haven't been able to accomplish what I need to do without one. So, I coded this process using cursors and performance (as expected) is pretty mediocre. I was wondering if someone could take a quick look and suggest a different approach or maybe suggest ways to optimize the current code.
*Attached* is my code.
TIA
View 4 Replies
View Related
Oct 24, 2005
Here's a sample SELECT statement:
SELECT T1.F3
FROM T1 INNER JOIN T2 ON T1.F4 = T2.F4
WHERE
(T1.F1 > @iNum AND T2.F1 > @iNum)
OR
( @iNum2 * (T1.F1 - T2.F1)/(T1.F2 - T2.F2) ) + (T1.F1 - ((T1.F1 - T2.F1)/(T1.F2 - T2.F2) * T1.F2) ) > @INum
As you can see, the second part of the WHERE (after the OR) is much more complicated than the part before the OR. My query would run a lot faster if it tried the first part of the OR and didn't bother with the second part if the first part was satisfied. Is there any way to do this?
Thanks!
Tyler
View 3 Replies
View Related
May 4, 2006
Kudos to y'all!!! I have this SQL script...
SELECT * FROM OPENQUERY (liorder, '
SELECT DISTINCT
a.AUF_NR AS OrdNo,
e.KU_NAME AS Customer,
a.AUF_POS AS Pos,
f.PC_PANE_NO AS Pane,
f.PC_SGGL_SEQ AS Component,
f.PC_SGGL_COD AS GlassCode,
d.GL_BEZ AS GlassDesc,
a.ANZ AS Qty,
((c.BREITE/1000*c.HOEHE/1000)*a.ANZ) AS SQM,
(a.ANZ*c.SUM_BRUTTO) AS Val,
(CASE
WHEN(SELECT SUM(h.KF_FERT_QTY)
FROM LIPROD.KAPA_AUS_FERT h
WHERE a.AUF_NR = h.KF_ORDER_NO AND
a.AUF_POS = h.KF_ORDER_POS AND
f.PC_PANE_NO = h.KF_SCHEIB_NR AND
f.PC_SGGL_SEQ = CASE
WHEN h.KF_SEQ_NR = 0 THEN 1
ELSE h.KF_SEQ_NR
END AND
h.KF_SCHR_NR IN (2, 402, 502, 602)) IS NULL THEN 0
ELSE(SELECT SUM(h.KF_FERT_QTY)
FROM LIPROD.KAPA_AUS_FERT h
WHERE a.AUF_NR = h.KF_ORDER_NO AND
a.AUF_POS = h.KF_ORDER_POS AND
f.PC_PANE_NO = h.KF_SCHEIB_NR AND
f.PC_SGGL_SEQ = CASE
WHEN h.KF_SEQ_NR = 0 THEN 1
ELSE h.KF_SEQ_NR
END AND
h.KF_SCHR_NR IN (2, 402, 502, 602))
END) AS Done
FROM LIORDER.AUF_STAT a,
LIORDER.AUF_KOPF b,
LIORDER.AUF_POS c,
LIORDER.GLAS_DATEN d,
LIORDER.KUST_ADR e,
LIPROD.AUF_POS_COMP f
WHERE EXISTS
(SELECT g.AUF_NR
FROM LIORDER.AUF_STAT g
WHERE g.AUF_NR = a.AUF_NR AND
g.RG_OFFEN != 0) AND
EXISTS
(SELECT i.KF_ORDER_NO
FROM LIPROD.KAPA_AUS_FERT i
WHERE a.AUF_NR = i.KF_ORDER_NO AND
i.KF_SCHR_NR IN (2, 402, 502, 602)) AND
a.AUF_NR = b.AUF_NR AND
b.AUF_NR = c.AUF_NR AND
c.AUF_NR = f.PC_ORDER_NO AND
a.AUF_POS = c.AUF_POS AND
c.AUF_POS = f.PC_ORDER_POS AND
b.KUNR = e.KU_NR AND
f.PC_SGGL_COD = d.IDNR AND
a.HISTORY = 0 AND
b.AUF_OFF = 0 AND
c.VER_ART != ''V'' AND
e.KU_VK_EK = 0 AND
e.KU_NAME IS NOT NULL
ORDER BY a.AUF_NR DESC,
a.AUF_POS ASC')
...It is retrieving data from an Oracle linked server. But the execution time is so friggin' long! I tried running it and for around 30 minutes it hasn't shown any results. So I could even tell the exact time it would take to return results. Do you have any tips regarding query optimization? Thanks in advance.
View 9 Replies
View Related
May 28, 2004
we have an insurance program up and running in our regions and we get random reports of slowness. in an effort to track down all facets of slowness i am looking at all my sql code to make sure it is as efficient as possible. I know a little about SQL and writing SQL statements, enough to help me do my job well. but i do not write optimized code.
if request.form("selPolicyNum") <> "" then
sqlPolicyInfo = "SELECT PIEffectiveDate, PIExpirationdate from PIMaster where PIPolicyNum='" & request.form("selPolicyNum") & "'"
Set rsPolicyInfo = Server.CreateObject("ADODB.Recordset")
Set rsPolicyInfo.ActiveConnection = webLookupConn
'rsPolicyInfo.CursorType = adOpenDynamic
'rsPolicyInfo.LockType = adLockOptimistic
rsPolicyInfo.Source = sqlPolicyInfo
'rsPolicyInfo.CursorLocation = adUseClient
rsPolicyInfo.Open
'response.write sqlPolicyInfo
end if
if request.form("selPolicyNum") <> "" then
sqlRemarkInsert="INSERT INTO clRemarks (PolicyNum, AccountNum, UserID, RemarkBody, CreationDate, PolEffectiveDate, PolExpDate, RemarkCategory1, RemarkCategory2, RemarkCategory3, RemarkCategory4, RemarkCategory5, RemarkCategory6, RemarkCategory7, RemarkCategory8, RemarkCategory9, RemarkCategory10, RemarkCategory11, RemarkCategory12, RemarkCategory13) VALUES ('"
sqlRemarkInsert=sqlRemarkInsert & request.form("selPolicyNum") & "', '" & request.form("txtAcctNum") & "', '" & request("aysmenu")("userid") & "', '" & Replace(request.form("RemarkBody"),"'","''") & "', '" & CurrDate & "', '" & rsPolicyInfo("PIEffectiveDate") & "', '" & rsPolicyInfo("PIExpirationDate") & "', '" & request.form("chkIssuingInstructions") & "', '" & request.form("chkLossControl") & "', '" & request.form("chkLossHistoryClaims") & "', '" & request.form("chkReinsurance") & "', '" & request.form("chkMVRDriverIssues") & "', '" & request.form("chkBilling") & "', '" & request.form("chkGeneral") & "', '" & request.form("chkCancellationDNR") & "', '" & request.form("chkPolicyAmendments") & "', '" & request.form("chkExperienceRatingInfo") & "', '" & request.form("chkDiscretionaryPricing") & "', '" & request.form("chkPremiumAudit") & "', '" & request.form("chkQuotingInstructions") & "')"
else
sqlRemarkInsert="INSERT INTO clRemarks (AccountNum, UserID, RemarkBody, CreationDate, RemarkCategory1, RemarkCategory2, RemarkCategory3, RemarkCategory4, RemarkCategory5, RemarkCategory6, RemarkCategory7, RemarkCategory8, RemarkCategory9, RemarkCategory10, RemarkCategory11, RemarkCategory12, RemarkCategory13) VALUES ('"
sqlRemarkInsert=sqlRemarkInsert & request.form("txtAcctNum") & "', '" & request("aysmenu")("userid") & "', '" & Replace(request.form("RemarkBody"),"'","''") & "', '" & CurrDate & "', '" & request.form("chkIssuingInstructions") & "', '" & request.form("chkLossControl") & "', '" & request.form("chkLossHistoryClaims") & "', '" & request.form("chkReinsurance") & "', '" & request.form("chkMVRDriverIssues") & "', '" & request.form("chkBilling") & "', '" & request.form("chkGeneral") & "', '" & request.form("chkCancellationDNR") & "', '" & request.form("chkPolicyAmendments") & "', '" & request.form("chkExperienceRatingInfo") & "', '" & request.form("chkDiscretionaryPricing") & "', '" & request.form("chkPremiumAudit") & "', '" & request.form("chkQuotingInstructions") & "')"
end if
rsRemarkInsert=ComProConn.Execute (sqlRemarkInsert)
rsPolicyInfo.close
Set rsPolicyInfo=nothing
that is the code used to store a remark into the system. is this code optimized already or should some of the db parameters be changed to make things faster? this is just an example of many of the SQL statements that i may or may not have to fix. thank you for any and all help.
View 1 Replies
View Related
Jun 23, 2008
I have the following query that works fine but i'm wondering if there is a way to optimize it better as when I analyze through sql profiler it is at the top of the list of using the cpu
SELECT DISTINCT site, d, (SELECT COUNT(id) FROM anP aPV2 WHERE aPV2.confirmed=1 and aPV2.stage=2 and aPV2.inserted=0 and aPV2.site=aPV1.site and aPV2.d>=aPV1.d and aPV2.d<=aPV1.d) AS mycount FROM anP aPV1 WHERE confirmed=1 AND stage=2 AND inserted=0 ORDER BY site,d
View 8 Replies
View Related
Jan 3, 2007
this code takes far too long to run any suggestions:
create table #minatest(
DatabaseName varchar(100),
pagecount varchar(100),
vfillfactor varchar(100),
TableName varchar(200),
IndexName varchar(100),
FragmentPercentage int,
newfragmentvalue int,
index_type_desc varchar(100),
index_level int
)
-- minatest table will contain indexes with fragmentation above 10% which need to be defragged
-- this will go through all databases
-- null indexes will not be affected
exec sp_msforeachdb'
use ?
INSERT INTO #minatest
SELECT
db_name(database_id),
phystat.page_count,
i.fill_factor,
OBJECT_NAME(i.object_id),
i.name,
phystat.avg_fragmentation_in_percent,
newfragmentvalue = 0,
index_type_desc,
index_level
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, ''DETAILED'') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.page_count < 10000
'
DECLARE @Counts int,
@i int,
@DatabaseName varchar(100),
@pagecount varchar(100),
@vfillfactor varchar(100),
@TableName varchar(200),
@IndexName varchar(100),
@sql nvarchar(4000),
@nfsql nvarchar(4000),
@FragmentPercentage int,
@params nvarchar(4000),
@index_type_desc varchar(100),
@index_level int,
@vnewfrag int
SELECT @params = N'@cnt int OUTPUT'
select @Counts = count(Databasename)
from #minatest -- sets the maximum amount of fields to go threw as a number
declare targets cursor -- declare cursor with values to search through
for
select * from #minatest
open targets -- open cursor
fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level-- take rows from table
select @i=0
while @@fetch_status=0 and @i<=@Counts-- set loop condition
begin
select @sql = 'USE '+@DatabaseName+'; '+
' ALTER INDEX '+@IndexName+' ON '+ @TableName+
' REBUILD with (ONLINE=ON,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=OFF);'
exec sp_executesql @sql
select @nfsql = 'select @cnt = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL,NULL, NULL, NULL, ''DETAILED'') phystat JOIN '+@DatabaseName+'.sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE i.name='''+@IndexName+''' and index_type_desc='''+@index_type_desc+''' and index_level='''+CAST(@index_level as varchar(20))+''''
exec sp_executesql @nfsql ,@params, @cnt=@vnewfrag OUTPUT
update #minatest set newfragmentvalue = @vnewfrag where IndexName = @IndexName and TableName = @TableName
select @i=@i+1
fetch next from targets into @DatabaseName,@pagecount,@vfillfactor,@TableName,@IndexName,@FragmentPercentage,@vnewfrag,@index_type_desc,@index_level-- take next field of table
end
close targets
DEALLOCATE targets
ALTER TABLE #minatest DROP COLUMN index_type_desc,index_level
select * from #minatest -- displays which indexes where defraged and their new frag value
drop table #minatest
View 5 Replies
View Related
Sep 18, 2007
Hi,
I have the below query written so that i do not insert entries that is already existing in the table. I am trying to put in 70000 entries at a single shot and it breaks down. Can anybody help me optimize the below query so that it doesnt break? Is there any other way I can write this query?
Please do help me with this. Thanks in advance. The table in which i am inserting the entries has a composite key composed of ACCT_NUM_MIN and ACCT_NUM_MAX. I am getting this from a table which doesnt have a primary key(CORE)
INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
SELECT UID , LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), BIN, BUS_ID, BUS_NM, ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE o
WHERE NOT EXISTS (SELECT * FROM CRF i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN
AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)
View 20 Replies
View Related
Mar 1, 2006
I have two tables.One has approx 90,000 rows with a field .. let's call in BigInt (and itis defined as a bigint data type).I have a reference table, with approx 10,000,000 rows. In thisreference table, I have starting_bigint and ending_bigint fields. Iwant to pull out all of the reference data from the reference table forall 90,000 rows in the transaction table where the BigInt from thetransaction table is between the starting_bigint and ending_bigint inthe reference table.I have the join working now, but it is not as optimized as I wouldlike. It appears no matter what I do, the query does a full table scanon the 10,000,000 rows in the reference table.Sample codeSELECT ref.*, tran.bigintfrom transactiontable tranINNER JOIN referencetable ref on tran.bigint betweenref.starting_bigint and ending_bigintYes, all 3 of the fields are indexed. I even have a composite index onthe reference table with the starting_bigint and ending_bigint fieldsselected as the composite.Any help would be appreciated.Robert H. KershbergIT DirectorTax Credit CompanyJoin Bytes! or Join Bytes! or Join Bytes!
View 5 Replies
View Related
Mar 29, 2006
Hello all,I have a table with thousands of rows and is in this format:id col1 col2 col3 col4--- ------ ----- ------ ------1 nm 78 xyz pir2 bn 45 abc dirI now want to get the data from this table in this format:field val---------------------------col1 nmcol1 bncol2 78col2 45col3 xyzcol3 abccol4 pircol4 dirIn order to do this I am doing a union:select * into #tempUpdate(select 'col1' as field, col1 as val from table1unionselect 'col2' as field, col2 as val from table1unionselect 'col3' as field, col3 as val from table1)the above example query is smaller - I have a much bigger table withabout 80 columns (Imagine the size of my union query :) and this takesa lot of time to execute. Can someone please suggest a better way to dothis?The results of this union query are selected into a temp table, which Ithen use to update another table. I am using SQL Server 2000.my main concern is performance. any ideas please?thanks
View 6 Replies
View Related
Feb 9, 2007
To start with, I'll give a simplified overview of my data.BaseRecord (4mil rows, 25k in each Region)ID | Name | Region | etcOtherData (7.5mil rows, 1 or 2 per ID)ID | Type(1/2) | DataProblemTable (4mil rows)ID | ConcatenatedHistoryThe concatenated history field is a nvarchar with up to 20 differentpipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|11/12/2004Q|'Using left outer joins (all from base, the rest optional) I've got aview something like:View (4mil rows)ID | Name | Region | etc | Data | Data2 | ConcatenatedHistoryQuerying it, it takes about 15-20 seconds to do this:Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
Quote:
View 5 Replies
View Related
Jul 20, 2005
I have an application that's allows user input, and is translating it bystripping out the html tags and also doing some code translations. The useris able to later edit their input. However it's unfeasible to reversetranslate it back as the logic would be too complicated, and there areinstances where it won't be possible.So, what I'm thinking to do to speed up performance is to duplicate the userdata, one for native data, and the other for the translated data. When useredits their input, the native data is shown. When the application isshowing the data in a page, the translated data is shown.My question is, would it make a performance difference if I store the nativedata and the translated data in the same table, or would it be better tostore the cached data in another table?
View 1 Replies
View Related
Feb 2, 2007
if I have the following tables:
Table Customer_tbl
cust_id as int (PK), ...
Table Item_tbl
item_id as int (PK), ...
Table Selected_Items_tbl
selected_item_id as int (PK), cust_id as int (FK), item_id as int (FK), ...
-------
With the following query:
select cust_ID from selected_items_tbl WHERE item_id in (1, 2, n) GROUP BY cust_id, item_id HAVING cust_id in (select cust_id from selected_items_tbl where item_id = 1) AND cust_id in (select cust_id from selected_items_tbl where item_id = 2) AND cust_id in (select cust_id from selected_items_tbl where item_id = n)
-------
Each of these tables has other items included. Selected_Items_tbl holds zero to many of the items from the item_tbl for each customer. If I am searching for a customer who has item 1 AND item 2 AND item n, what would be the most efficient query for this? Currently, the above query is what I am working with. However, it seems that we should be able to do this type of search in a single query (without subqueries).
View 3 Replies
View Related
