Does Using Sql Parameters Speed Asp.net Code Up?
Mar 4, 2004does using sql parameters speed asp.net code up?
View 3 Replies
ADVERTISEMENT
¿What Improves SQL Server Performance? HD Speed, Processor Speed Or Ram?
Oct 18, 2007
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
Report Parameters Using Code
Feb 21, 2008Hello Everyone,
I would like to create a report parameter that will allow the user to select a week from a list of all weeks so far this year.
For example:
First Parameter is Week: (Week 1, Week 2, ... , Week 7)
Second Parameter is Year: 2008
How can I accomplish this? Can I create a function and use it as a parameter query?
Thank You in advanced
Passing Parameters For SqlDataSource From Code Behind
May 25, 2007Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
View 8 Replies View RelatedSqlDataSource Pass Parameters From Code-behind
Jul 15, 2007how do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
Accessing Parameters In Custom Code
Feb 21, 2008Hi,
I am trying to retrieve and manipulate the value of the stored procedure parameters in the following custom code:
Code Snippet
Public Function ShowLoading(ByVal intOption as integer, byVal BasicLoading as string, byVal BasicHealthExtra as string, byVal RiderLoading as string, byVal RiderHealthExtra as string, byVal TotalAmount as string) As string
dim strRider as String
dim strCompanyName as string
strCompanyName = Fields!CompanyName.Value
select case strCompanyName
case "and Plus Rider ":
strRider = "Plus Rider"
case "and Assist Rider ":
strRider = "Assist Rider"
end select
return strRider
End Function
However, i got this error when I try to preview the report: An error occurred during local report processing, The definition of the report :
'/reportC' is invalid. There is an error on line 5 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
Any help is greatly appreciated. Thanks in advance.
Auto Generate Code For Sp Parameters?
Apr 18, 2007Is there an easy way to generate the parameters code for calling stored procedures?
Is there an easy way to generate stored procedure code without having to manually type it in? I've seen 3rd party tools, but I was wondering if there's anything within Sql Server to do it.
I'm using c#, visual studio 2005, sql server 2005.
Thanks,
John
DtsConnection -- Consuming DTS DataReader In Code W/ Parameters
Sep 27, 2005I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following
View 9 Replies View RelatedWhat Is Wrong With This Code? Selecting Data With Stored Procedure With Multiple Parameters
Sep 22, 2005oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
Calling A Code Block Using Parameters In A Report Of Sql Server Reporting Services
Dec 18, 2007
Hi
This is the code which I have written in code window.
Public Shared Function CalcLocalFactor(ByVal CalcLifeCode As Integer, ByVal CalcFiscalAge As Integer, ByVal CalcLifeYearsUsed As Double, ByVal CalcLocConvention As String, ByVal CalcSRate As Integer) As Double
Dim locCalcFiscalAge As Integer = 0
Dim locFactor As Double= 1.0
Dim locFactor1 As Double = 1.0
Dim REM1 As Integer = 1
Dim DEP As Double = 0
Dim YR As Integer
Dim HALF_YEAR As Double
Dim LINEAR As Double
Dim MACR As Double
If CalcFiscalAge > CalcLifeCode + 1 Then
locCalcFiscalAge = 0
locFactor = 1.0
End If
If (CalcLocConvention <> "HALF-YEAR" And CalcLifeYearsUsed < CalcLifeCode) Then
locFactor = Math.Round((CalcLifeYearsUsed / CalcLifeCode), 4)
End If
If (CalcLocConvention = "HALF-YEAR") Then
for YR = 1 to CalcFiscalAge step 1
If YR = CalcLifeCode + 1 Then
locFactor1 = 1
Exit For
End If
If (YR = 1 Or YR = CalcFiscalAge) Then
HALF_YEAR = 2
Else
HALF_YEAR = 1
End If
LINEAR = Math.Round(REM1 / (CalcLifeCode - YR + 1.5) / HALF_YEAR, 4)
MACR = Math.Round(REM1 / CalcLifeCode * CalcSRate / HALF_YEAR, 4)
If MACR >= LINEAR Then
DEP = MACR
Else
DEP = LINEAR
End If
locFactor1 = locFactor1 + DEP
REM1 = 1 - locFactor1
locFactor = locFactor1
Next
End If
Return locFactor
End Function
I'm calling this code in a Report Parameter like below:
=Code.CalcLocalFactor(Parameters!CalcLifeCode.Value,Parameters!CalcFiscalAge.Value,Parameters!CalcLifeYearsUsed.Value,Parameters!CalcLifeYearsUsed.Value, Parameters!CalcSRate.Value )
It is working fine for the first record where as for other records, the value is not getting changed. i.e. the first records value is coming repeatedly for all other records also.
How can I dynamically change the parameter values of the function?
Parameter is not accepting directly the field names, hence I used other parameter to initialize the field and used that parameter for this.
Ex. Parameter Name ; FieldPurchDate (internal) FieldName : PURCHDATE
Other parameter: FieldInDate (internal) FieldName : InDate
While initializing the new parameter CalcPurchDate,, I used an expression for this: Parameters!CalcFiscalAge.Value
=iif(Parameters!FieldPurchDate.Value is nothing, Parameters!FieldInDate.Value,Parameters!FieldPurchDate.Value)
and using this CalcPurchDate for processing of the parameter:
These are some of the things , I am doing ....
Please let me know how to fix this issue...
Thanks in advance
Regards,
Radhika
Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005
Jul 27, 2006Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Mar 28, 2007Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
SQL Speed
Sep 19, 2006Andy writes "I have data with more than 4 milions. How to speed up query it ?"
View 3 Replies View RelatedSpeed
Mar 9, 2007hello,
i need some opinion on how to sum up or group by more than 2k records faster.. eg, how do i optimize this?
SELECT DISTINCT r.ClientID,c.ClientName, r.ItemID, r.StockID,r.StockName, r.ExpectedQty,r.QCQty,r.AVAQty,r.PNDQty as pnd, r.VMIQCQty,r.VMIAVAQty,r.VMIPNDQty as vmipnd,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownerstatus='VMI') AS VMIPNDQty,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='VMI') AS PNDQty,
(Select isnull(SUM(d.OriginQty - d.PickQty -d.HoldQty -d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustAVAQty,
(Select isnull(SUM(d.HoldQty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustPNDQty,
(Select isnull(SUM(d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustQCQty
FROM tblItemCrossRef r
INNER JOIN tblClient c ON c.ClientID=r.ClientID
INNER JOIN tblItemClients i on i.Supplier=r.ClientID
WHERE r.ClientID=@ClientID AND r.StockID LIKE @StockID+'%'
~~~Focus on problem, not solution~~~
Speed Up UDF
Jun 22, 2006Hello all-Given the following UDF, in sql 2000 can it be sped up, complied oranything of the like. A query returning 300,000 + rows times out whenran through the udf, inline case statements returns the rows in 5seconds.Thanks!JeffCREATE FUNCTION dbo.TimeFormat(@input datetime,@groupformatvarchar(20) --DAY, WEEK, MONTH)RETURNS datetimeASBEGINdeclare @dtvar as datetimeif @groupformat = 'DAY'set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)else if @groupformat = 'WEEK'set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),@input, 101)), CONVERT(char(10), @input, 101)) AS datetime)else if @groupformat = 'MONTH'set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)return @dtvarEND
View 9 Replies View RelatedI Need BETWEEN On Speed
Jul 20, 2005This is x-posted in:alt.php.sqlcomp.databases.ms-sqlservermicrosoft.public.sqlserver.programmingI have events that occur during the day. I want to be able to search thoseby a form with checkboxes (multiple select).Let's say for instance an event is happening from 3-10pm. When someonesearches for 4-6 (checkbox option) it needs to show up.I don't need code so much as I just need theory. My theory that I coded outand worked, just a missight in theory is as follows. I did a BETWEEN callthat pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.As you can see. The event spans that time, but does not start or stopbetween 4 and 6, thus was not pulled. Ooops.So if someone call tell me of another function or perhaps just a better wayto use BETWEEN that would be great. I don't think that code is necessary atthis juncture, so save the 'Please post code' post :) Thanks.
View 12 Replies View RelatedADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure
Mar 12, 2008Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
Sp_executesql And Speed
Jul 12, 2006Hello,
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
Speed Up Query
Jan 17, 2007Hi,
Can anyone tell me a way to speed up these querys?
//This is selecting a number of records (sent by user) from a table and randomizing those
tempSQL.Text = "select top " + amount.Text + " number from [" + src.Text + "] Where pull='N' order by newID()";
SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2.Open();
SqlCommand cmd3 = new SqlCommand(tempSQL.Text, conn2);
cmd3.CommandTimeout = 1000;
SqlDataReader dr = cmd3.ExecuteReader();
//Then I open a data reader that uses the records
SqlConnection conn2a = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2a.Open();
while (dr.Read())
{
//the records are then placed 1 by one into a temp table
string fillresultID = "Insert into [" + src.Text + "_Additional_Temp] (number) Values('" + dr["number"] + "')";
SqlCommand cmd4 = new SqlCommand(fillresultID, conn2a);
cmd4.CommandTimeout = 0;
cmd4.ExecuteNonQuery();
//then the original table that held the numbers is marked as used(again one by one)
string update = "Update [" + src.Text + "] set pull='Y' where number='" + dr["number"] + "'";
SqlCommand cmd5 = new SqlCommand(update, conn2a);
cmd5.CommandTimeout = 0;
cmd5.ExecuteNonQuery();
}
dr.Close();
conn2.Close();
conn2a.Close();
Thanks,
Doug
How Can I Speed Up This Query
Apr 22, 2008Hi, how can i speed up this query, it seems to be taking a very long time to bring back the reults;
--This stored procedure retrieves access rights for usersCREATE PROCEDURE wc_User_Access_Right_List
ASSELECT dbo.tblRep.Rep_ID, RTRIM(dbo.tblRep.Rep_Forename) + ' ' + RTRIM(dbo.tblRep.Rep_Surname) AS User_Full_Name, dbo.tblAccessRight.Access_Right, dbo.tblAccessRight.Access_Right_IDFROM dbo.tblRep LEFT OUTER JOIN dbo.tblAccessRight ON dbo.tblRep.Access_Right_ID = dbo.tblAccessRight.Access_Right_ID ORDER BY User_Full_Name
--Make sure this has saved, if not return 10 as this is unexpected error
IF @@rowcount = 0 return 10
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
Variables And Speed
May 28, 2002I have a select statement that has many 'ands' where one side uses the same thing for ex.
where
t1.column1=r3.other and
t1.column1=e5.new and
t1.column1=k9.old etc...is there any speed gained by putting the value for column one into a variable and using that variable each time instead of t1.columns1?
Thanks,
Eddie
Optimizing Speed
Sep 4, 2001I 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 RelatedTables And Speed
Dec 10, 1999I am in the planning stages of a website and want to design my database to allow for optimum performance in case the site becomes popular. In my database I will have around 5 main categories with each category having around 25 or 30 subcategories and each subcategory having around 100 to 300 items. I plan to use MS-SQL Server7. Would it be best to have one large table or have 5 tables for the main categories or have around 100 tables one for each subcategory? Database usage will be simple (no complex queries) but most pages will hit the database and I need to allow for a potential of 4 or 5 million page views per month. The SQL Server will be a shared one.
Randall
Speed Up Blobs
Jan 4, 1999Howdy,
Any one got any tricks on speeding up storing and retrieving image data? I have a typical 2MB image and I am trying to improve speed... it now takes 8 seconds to store the blob.
thanks
Dick Butler
Speed Issues With MS Sql
Jul 15, 2003I have migrated a database from ms access to ms ms sql. But I am encountering a problem with the website loading speed. It was running much faster with ms access then it is running with ms sql.
Any inputs or thoughts are appreciated.
Treat it as urgent.
Backup Speed
Apr 1, 1999We are currently running Backup Exec v7.0 and are backing up two SQL 6.5 servers on our network. The backups
have begun taking excessive amounts of time considering the limited amounts of data on the servers. Does anyone have
an idea of parameters we could check on the servers or other settings that should be checked in Backup Exec? Also, this
problem seems to have surfaced after we loaded Network Associates VirusScan NT onto our NT Servers and Workstations.
Has anyone had problems with this product on their SQL servers?
MSSQL Speed
Aug 1, 2006Hi
I've recently been given the task to maintain a website that runs off mssql using vb and asp .net. One of the main task is to improve the mssql access time. I am fairly new to mssql, can anyone give me some insight on the procedure to improve access time to mssql. Obviously sql query plays a big part, but what about database config, asp/vb .net commands or design patterns (for large amount of data pulling and join?)
Any suggestions or pointer will be greatly appreciated.
How Do I Speed Up An UPDATE?
Aug 28, 2004I have a single UPDATE statement that has been running for 13+ hours and I have no idea of when it will complete.
Recipients has 80 million records
UpdatedStagingRecipients has 34 million records.
Why would this possibly take so long? Is there anything that I can do at all?
UPDATE Recipients
SET Recipients.First = UpdatedStagingRecipients.First
, Recipients.Last = UpdatedStagingRecipients.Last
, Recipients.StreetAddress = UpdatedStagingRecipients.StreetAddress
, Recipients.City = UpdatedStagingRecipients.City
, Recipients.State = UpdatedStagingRecipients.State
, Recipients.Postal = UpdatedStagingRecipients.Postal
, Recipients.Country = UpdatedStagingRecipients.Country
, Recipients.DOB = UpdatedStagingRecipients.DOB
, Recipients.Obscene = UpdatedStagingRecipients.Obscene
, Recipients.Gender = UpdatedStagingRecipients.Gender
, Recipients.IPv4 = UpdatedStagingRecipients.IPv4
, Recipients.NameSourceID = UpdatedStagingRecipients.NameSourceID
, Recipients.NameLine = UpdatedStagingRecipients.NameLine
, Recipients.AddressSourceID = UpdatedStagingRecipients.AddressSourceID
, Recipients.AddressLine = UpdatedStagingRecipients.AddressLine
, Recipients.RecordCreationSourceID = UpdatedStagingRecipients.RecordCreationSourceID
FROM Recipients INNER JOIN UpdatedStagingRecipients ON (Recipients.UserName = UpdatedStagingRecipients.UserName AND Recipients.DomainID = UpdatedStagingRecipients.DomainID)
TRUNCATE TABLE UpdatedStagingRecipients
Any Ideas On How To Speed Up This Sp?
Jan 18, 2006it is working but takes about 3-4 seconds per exec.
CREATE PROCEDURE isp_ap_calc_apt_totals
@p_comp char(2),
@p_vend char(6),
@p_asofdatechar(8)
as
if (@p_asofdate <= '00000000')
begin
set @p_asofdate = '99999999'
end
delete from XAPAPTTOT
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @p_comp) and (a.apph_vend = @p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate) and a.apph_unpost_dt > @p_asofdate and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
GO
Increasing Speed
Jan 25, 2006I'm not sure if this is the right forum, but I have a general question about running/storing databases. I have been running a process with 60+ million records in one table and another 16 million in another table and it is taking forever to get everything imported in and run the appropriate queries. I've been doing this all on a desktop and I am anxious to learn of a more efficient, faster method of processing this amount of data.
What solution should I pursue if I am doing this work a few times a year so that it doesn't take three full days of processing to reach an answer with the data?
Thanks.
Encryption Speed?
Aug 28, 2006Has anyone started using encryption yet? Is it noticably slower than not using it? For the record Im not refering too column encryption, but "network" (not sure what else to call it) encryption when it's encrypted between SQL Server and the client.
View 1 Replies View RelatedSpeed Tests (in Vs. Specified)
Jul 30, 2007Hi Everyone -
We were discussing select speeds the other day,
the question comes in at...
what is faster, a standard query with specifies the search critera
i.e. select * from xyz where a='A' or a='B'
or is the IN a better way to go....
select * from xyz where a in ('A', 'B')
we ran the tests in performance analyzer,
but they were the same results....
maybe i'm smoking the stuff - but i thought the
useage of the IN causes a full table scan for each
of the items in the in clause
please advise
take care
tony
DB Connection Speed
Mar 1, 2004This may be more appropriate on another forum, but wanted to see if anyone could give me any ideas.
We have SQL 7 on NT4. We are restricted from upgrading for a couple of particular reasons, but things nevertheless run fairly stable.
We have roughly 100 - 140 simultaneous connections.
Some users have very slow access times where others have a fast connection time. This is not restricted to DB access, but also for file transferring.
Just like you can test your DSL/Cable speeds, are there any similar tests that can be ran on client machines to find their access speeds to the SQL database server?
I really appreciate your suggestions!