Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
Cheers,
Celia
I am developing Payroll Solution for a large government department. I have created following tables:
Table: Allowance Master (AllowanceID smallint P.K, AllowanceName varchar(30) unique)
Table: DeductionMaster (DeductionID smallint P.K, DeductionName varchar(30) unique)
Table: EmpMaster (EmpCode LongInt P.K, EmpName varchar(60))
Table: Emp_Pay_Details (Salary_Month_Year Date, Emp_Code, Tran_Detail varchar(30), Tran_Type char(1), Amount number(20,2))
In Emp_Pay_Details table, the TranDetail field stores the name of Allowance/Deduction and TranType field stores A or D, for Allowance or Deduction. The sample records will look something like this:
SALARY_MONTH_YEAR EMPCODE TRAN_DETAIL TRAN_TYPE AMOUNT
-----------------------------------------------------------------------------------------------------------------------------------
Jan-2007 101 DEARNESS A 100
Jan-2007 101 VEHICLE A 200
Jan-2007 101 RECOV. D 5000
As you can see in the above table, the pay record for the month Jan-2007 is entered. Though I have shown only three rows. In real there are compulsorily 15 rows for each employee. These 15 rows shall be entered per month for each employee. Though we have taken Allowance Master table and Deduction Master table where we will store one time values for the total number of Allowances/Deductions that need to be applied on the month salary. The Emp_Pay_Details table stores the exact record of the salary.
In the Emp_Pay_Table, I have used Tran_Detail field to store either the name of Allowance (from Allowance Master table) or the name of Deduction (from Deduction Master table) and the Tran_Type column stores A or D for Allowance or Deduction.
Since this table will store 15 records for each employee per month and there are 5000 employees, so 15*5000 = 75,000 rows shall be inserted each month. Depending upon the growing size of the table, I want to know:
(1) How to optimize the table structure for better performance?
(2) Whether I should take a composite key of (Salary_Month_Year, EmpCode, Tran_Detail) as Primary Key or any other way?
(3) Any other SQL Server 2005 feature to use while fetching records from this table.
Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
Hi anyone can tell me how to create a table from an existing table in MSSQL.
In oracle we issue " create table <tablea> as select * from <tableb>; " ?
Thanks in Advance.
I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber Product OrderQuantity--------------- --------------- ----------------------0608 Prod1 30608 Prod2 120608 Prod3 2 What I am after is for the results to display something like this.OrderNumber Prod1 Prod2 Prod3
--------------- --------- --------- ---------
0608 3 12 2 This is using SQL Server ver 8.0
In order to update a MSSQL table, named [Table_1]. The procedure is:
(@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1:
UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id
Method 2
IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id
Method 3:
DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
Which is the best? Thanks!
Hello all,
I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.
Currently, I would like to do the following (in abstract terms):
declare tmp record
select column1 from tableA into tmp
for each entry from above selection do
insert into tableB values (tmp[column1], 0, 0, 0)
I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:
CREATE FUNCTION dbo.newDay (@mDate datetime)
RETURNS int
AS
BEGIN
DECLARE @id int
DECLARE item_cursor CURSOR FOR
SELECT id FROM tblKitchenCat
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate)
FETCH NEXT FROM item_cursor INTO @id
END
CLOSE item_cursor
DEALLOCATE item_cursor
RETURN 0
END
GO
I get a syntax error next to AS... what is it?
Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.
In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?
Thanks in advance.
Cheers,
Michael
Hello all,
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users
ID
Username
ChangeLog
ID
Description
UserID
DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM Users
LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID)
ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged
FROM ChangeLog
LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID)
ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
Hello,
In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.
In MSSQL2005, I am not able to transfer files from one db to other.
Please advise.
Thanking You.
Regards,
Edward
I am new to SQL Server.
I have a fox pro database that is part of a vendor's product. I want to link some of the individual tables in the foxpro database in my SQL database.
Can someone point me to a tutorial or example on how to do this?
Thanks!
(i am not sure which forum to post this too)
Hi, I imported a table onto my host's server, table_Login which has username password, and userID. userID is Primary Key & identity.
When I had my code do insert of new user, I was getting error the Login.userID doesn't allow null values. Which shouldn't have mattered because userID is an identity.
After making a diagram from Enterprise Manager, I noticed that I have two Login tables (although only one shows in Enterprise Manager's "Table" node view. The second table, which had none of the constraints that my table of the same name which is listed under the "Table" node does; appears to be the table which is being written to by my code.
I can't find this table, Login(dbo), through Enterprise Manager, nor am i sure how to access it through Query Analyzer. Needless to say, I don't like this arrangement. and well tech support for this host seems to leave a lot to be desired.
Anyone know what needs to be done so that I can fix it or fight with tech support to convince them that they should?
Just like "describe select(*) from table_name" in DB2?
Thanks!
My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?
I want to insert arabic characters in my sql server db. I need create table and insert syntax to insert arabic characters.
Can anyone help me???
Dear All,
I have the table creation script and insret record script.
This is MySQl Format.
What changes I have to do so can I run this scripts into SQL Server 2000.
If any body has successfully done it then please tell me the procedure.
CREATE TABLE `activity` (
`id` bigint(20) NOT NULL auto_increment,
`object_type` varchar(60) default NULL,
`object_id` varchar(20) default NULL,
`person_id` bigint(20) NOT NULL default '0',
`activity_dtm` datetime NOT NULL default '0000-00-00 00:00:00',
`activity_type_cd` varchar(25) NOT NULL default '',
`description_code` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `FK9D4BF30FB4715636` (`activity_type_cd`),
KEY `FK9D4BF30F270CDEE0` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `activity`
--
-- --------------------------------------------------------
--
-- Table structure for table `actv_type`
--
CREATE TABLE `actv_type` (
`code` varchar(25) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`void_ind` char(1) NOT NULL default '',
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `actv_type`
--
INSERT INTO `actv_type` VALUES ('job_create', 'Created job', 'F');
INSERT INTO `actv_type` VALUES ('job_update', 'Changed job', 'F');
INSERT INTO `actv_type` VALUES ('job_void', 'Voided job', 'F');
INSERT INTO `actv_type` VALUES ('job_activate', 'Activated job', 'F');
INSERT INTO `actv_type` VALUES ('job_deactivate', 'Changed job to deactive', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_create', 'Created application', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_update', 'Updated application', 'F');
INSERT INTO `actv_type` VALUES ('intrv_create', 'Created interview', 'F');
INSERT INTO `actv_type` VALUES ('intrv_update', 'Updated interview', 'F');
INSERT INTO `actv_type` VALUES ('person_update', 'Update person', 'F');
INSERT INTO `actv_type` VALUES ('person_create', 'Create person', 'F');
INSERT INTO `actv_type` VALUES ('company_void', 'Voided company', 'F');
INSERT INTO `actv_type` VALUES ('company_create', 'Created company', 'F');
INSERT INTO `actv_type` VALUES ('company_update', 'Updated Company', 'F');
Thanks in Advance.
Hi,I'm doing an Orcle to SQL port.Simply all I want to do is run a script / DTS package that reads Oraclesequences value and writes the current value of it in to a MSSQL table - ofSequenceName, ValueCan this be done ? If so how ? (Please provide example)Steve
View 2 Replies View RelatedHere is my problem: I've created a table in MSSQL named MyReferences and then a typed dataset to connect to it.
Then I realized that I needed to add a column to MyReferences table and then I did it.
The problem is that I cannot find a way to add the new column to the typed dataset. I am unable to view it in the available columns.
Can anyone help me?
I'm using Visual Studio 2005 sp1 and C#.
Best regards
Alessandro
let's say I have this one, no problem:
Code Snippet
CREATE PROCEDURE au_info
@var text
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@var
PRINT CAST(@var as varchar(8000))
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/ROOT/Customers')
-- WITH Customers
GO
Code Snippet
EXEC au_info @var = '
<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
but what if I only have the xml text in a temporary table:
Code Snippet
create table #t1 (col1 text)
insert into #t1
Values('<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>')
if i do this:
Code SnippetEXEC au_info @var = (Select col1 from #t1)
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Code SnippetEXEC au_info (Select col1 from #t1)
Server: Msg 201, Level 16, State 3, Procedure au_info, Line 0
Procedure 'au_info' expects parameter '@var', which was not supplied.
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 RelatedHi 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
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 RelatedI'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!
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.
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
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
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.
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.
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
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
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)
hi all
View 8 Replies View RelatedI 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 RelatedHello 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