Datatable Getdata Performance - In-line Table Valued Function In SSMS Takes About 6 Seconds To Return 11088 Records?
Mar 25, 2011
An in-line table valued function in SSMS takes about 6 seconds to return 11088 records. The same function in VB .Net 4.0 using TableAdapters.InputTableAdapter.GetData (Created with data set designer) takes about 15 minutes complete.Both are executed from the same workstation.Why does this discrepancy exist?
Workstation:
Windows XP SP3, 2GB
VS2010 Ultimate
.Net 4.0 Framework
Server:
SQL Server 2008
View 3 Replies
ADVERTISEMENT
Jan 4, 2011
I am currently writing a scalar valued function and I'm having a few issue with the returned result. I have narrowed the problem down to a calculation that convert the difference between two dates as a percentage/decimal. No matter what I try the return value is always a whole number
set @earnedpremium = (@premium * @pretripearnings) + ((@premium - (@premium * @pretripearnings)) * cast((datediff(day, @outdate, @experiencedate) / datediff(day, @outdate, @returndate))as decimal(5,2)))
The cast section needs to return the percentage, I know the rest is working fine through some elimination and testing.
View 1 Replies
Nov 4, 2011
In my VB.net code i have:
cmd.CommandText = "SELECT * FROM [strfg].[dbo].[Myfunc] (@MyParam)"
Dim myparam As New SqlParameter("@MyParam", a)
cmd.Parameters.Add(myparam)
The function (runnig on SQL server) returns a table with four columns, one row.How do I call this function from Visual Basic? cmd.ExecuteScalar() is obviously not correct. In c++ I use a recordset. What is the equivalent in vb? Does anyone have a short example?
View 2 Replies
Mar 7, 2012
I've been looking at threading to make my webapplications more responsive. I've found out how to thread a sub that takes no variables and doesn't return anything, but I can't find out how to achieve this for a function that takes variables and returns something. here is an example
[Code]...
View 1 Replies
Jun 14, 2012
Quick question - if I have a timer set to 5000 (5 seconds) and the code in the timer takes longer than 5 seconds to run because of sql stuff, will the timer wait until the code is done till it fires again?
View 3 Replies
Mar 30, 2010
I want to return the top 5 records of a table in a dataset for datagrid view. The following does not work.
DataGridView.DataSource = DS.Tables("TABLENAME").Select("SELECT TOP 5")
View 4 Replies
Feb 29, 2012
I was trying to fill 10 checkedlistboxes with items..So I made a sub with 2 arguments like this Private Sub fill_checkedlistboxes(ByVal items As String, ByVal checkedlistbox As CheckedListBox) End sub the first argument items is a long string whitch splits in the sub into many strings and added them all into the second arguments (thecheckedlistbox) I want to make for next statement to fill all the 10 checkedlistboxes in my form with an 10 long strings held in an array called strings, so i write something like this [code] What i have to write in the "?????" area to have the checkedlistbox that i want? the checkedlistboxes names are checkedlistbox 1, 2 , 3 ....10
View 12 Replies
May 13, 2012
I run a simple SELECT (noted below) in a stored procedure of a table that's around 1,500 rows.
[Code]...
This operation takes around 7 seconds. I inserted Breakpoints and used F11 to determine that upon MySqlAdapter.Fill is where the lag starts. Both my computer and the server hosting the MySQL database are NOT challenged spec wise. I'm guessing it's the query itself. collectionitem holds the 2 foreign keys linking an itemproperty to a collection. we feed the sproc sId(PK of collection) so that the subquery returns all the itemids from a specific collection and then we use the itemid(PK) in itemproperty. Is there any way to speed up the process?
UPDATE My issue was entirely due to improper indexing. Once I learned which columns to index, everything is extremely smooth!
View 2 Replies
Apr 15, 2012
I am trying to execute a stored procedure which has table valued parameters along with other parameters. The only parameters I need to pass values for are latitude/longitude range and dates. For the others, I need to pass null. After a lot of searching and a lot of trial and error, I came up with the following:
Dim empty
As
IEnumerable =
Enumerable.Empty(Of
SqlDataRecord)()
I then pass empty for the table valued parameters. This works for the first two tvp's, but on the third one, I get this error:
There are no records in the SqlDataRecord enumeration. Tho send a table-valued parameter with no rows, use a null referece for the value instead.
how to make this work? I do not have control over the database or the stored procedure, so I can't change that. The code that calls the stored procedure is:
GetHistoryByMultipleCriteriaTableAdapter1.Fill(AISTable, empty, empty, empty, vbNullString, vbNullString, -60, -25, -35, 10, "4/1/2012 12:00 AM", "4/4/2012 12:00 AM", vbNullString)
I am not a developer, but an engineer who does development occasionally, so I may need more explanation than the average developer.
View 2 Replies
Dec 11, 2009
In my new application i have to process a SQL statement.Depending on the settings , the statement takes between 10 an 20 seconds.is there a possibility to display a second from / dialog / popup for this time?
View 5 Replies
May 25, 2011
I am interested in developing a multi-language program that will have a main .NET GUI but will perform calculations by calling procedures from a Fortran95 dynamic link library file. Right now I am just playing around with a very simple project in order to get the interoperability between the .NET framework and a Fortran95 (*.dll) file working correctly. So far, I've been able to pass the Fortran95 (*.dll) file an array from VB, sum all the elements of the array using an intrinsic Fortran function, and pass the scalar result back to the .NET framework with no problem. However, if I try passing Fortran the exact same array, construct the transpose of that array using another intrinsic Fortran function, and try passing the transposed array back to the .NET framework I keep throwing an exception. I am not a programming wiz, but it appears that when Fortran stores an array in memory, the .NET framework doesn't know how to read that. I am looking for some guidance on how to pass an array from the Fortran95 (*.dll) file back to the .NET framework.
View 9 Replies
Oct 14, 2010
Say you wanted to set a status message
' Set status
ssMainMessages.Text = msgValidating
Then perform an action that requires a http request that takes around 5 seconds then you want to display the result of that
' Set status
ssMainMessages.Text = msgValidated
It will only always display the second message? Its like it jumps the first part of the code and goes straight to httprequest.even if you put the update,system,threading.thread.sleep ....
View 2 Replies
Apr 25, 2010
When starting the default browser like this: Dim trgt1 As String = [URL]
[Code]...
View 10 Replies
Jul 15, 2011
Currently I'm writing a VB.NET app and it's getting big, resulting in its become very slow.Is there any application (or plug in) that can test the performance in seconds?I mean, when I click a button and it displays a product, I want to know exactly how long it is gonna take.
View 3 Replies
Dec 1, 2011
I am working with TVP and I am trying to pass a data table to the stored procedure as TVP. When the command tries to ExecuteNonQuery it throws an error:Operand type clash: datetime2 is incompatible with int. The data for table-valued parameter "@tvpPermitWork" doesn't conform to the table type of the parameter.I checked the data table using the visualizer and I find all the data to be correct. I am now stuck and I don't have the time to change it to stored procedures with individual parameters.
View 1 Replies
May 24, 2011
To have a function that takes the average (which is calculated by a different function) say for example the average is 6, now the function is to take the number 6 and get the variance from six to another number lets say its 12 so this will return a calculation of 50% variance. now here comes the issue, i have it calculating the variance how ever lets say that there is a score that is 0, how can i make it calculate the variance without taking count for that 0? i guess you can say i need it to "skip" if its a 0.
View 5 Replies
Dec 12, 2011
I use this code to return records in a DataGridView:
[Code]....
View 3 Replies
May 27, 2012
i'm making a utility in which i'm loading 100,000 records again and again, problem is that it takes much time to load records in datagridview every time.
i've used data set and data reader both but same result. if i use paging then there's will be a problem of search a record on keyup event.
View 5 Replies
Feb 2, 2012
I'm trying to make a program that takes my old records from Access and moves them to MySQL. Its supposed to take records from 3 different tables in Access to 3 different tables in MySQL. The first table is "Orders" and it sorts the ones to pull to the dataset, taxDS, by "Where Status = 'Waiting Payment' OR 'Cancelled'". Then the second table pulls its info(this is where i'm having trouble) by the "Control_Number" in the "Orders" table, so basically if the control number isnt already in the dataset its not going to pull the data into the new table "FNF Taxes".
Imports System.Data.OleDb
Imports MySql.Data.MySqlClient
Public Class Backup
[code]....
So now after my 3rd edit and no answers, I'm finally at the point where I'm only stuck on my For..Next statement not working, How can i get this statement to run for each 24,000 controlnumbers?
View 7 Replies
Apr 22, 2012
I have a database with 3 tables: Student, Unavailability and Duty.The fields for Student are entered in a form I have. One of the fields in Unavailability is filled by a form, the other fields are an autonumber and a foreign key to the Student table.ow I want records in the Duty table to be created and automatically filled in depending on what the values of the fields are in the Student table.For example, If the Boarder field in the Student table is 'yes' then I want the Duty Number field in the Duty table to be '1', '3' and '4'. If it is 'no' then I want the value to be '2' and '5'. I recognize it will have to create several different records to incorporate the different duty numbers for each StudentID. Obviously this will require an if statement, however this is my first time implementing a database with my limited experience with programming and Visual Basic, so I don't know how to refer to the specific fields in a table and set the value of other fields depending on the data in other fields.
View 6 Replies
Oct 13, 2011
DataTable1“ About 16 000 entries“ coming from SQL[code]...
I have tried using a NOT IN clause in my SQL query with all the entity numbers from table 2 in that query but it takes over 10 minutes to execute.
Using a For Each loop and comparing each row takes about 3 minutes
View 3 Replies
Oct 25, 2010
i have a datatable with about 10000 rows and few columns (4)I have noticed that the process of reading a big table its quite slow..is there a way to improve performance in reading a datatable?
View 15 Replies
Aug 29, 2011
I am having dataset with 5000 records.I am reading them in the below 2 functions and writing to excel.
FillDataRows1(worksheet)
FillDataRows2(worksheet)
private sub FillDataRow1(byval ws as worksheet)
[code]....
When I create thread also it is taking same time .
View 1 Replies
Aug 10, 2011
I have an asp.net application where I have a datatable ("DataTableA") that returns just one column ("ProductID").
I want to read each row of "ProductID", process some business logic, then copy both those columns (ProductID & ProductIDBusinessLogicValue) to DataTableB. This DataTableB is then displayed on the asp.net page.
What would bhe the best way to read each row of DataTableA ?
View 2 Replies
Mar 16, 2009
I have a program in where I need to loop though the entire dataset.datatable. I takes a long time. What is the alternative I can use to do the some job but faster?
View 4 Replies
Apr 30, 2009
from a performance point of view is it faster to work with linq to sql or dataset.datatable?
View 6 Replies
Jan 1, 2012
I have a function that takes a String as a parameter And in it I have variables, so if I call it it's like
Dim Text As String = MyStringConverter("Hello Mr. " & Name & " " & Surname "!")
View 4 Replies
May 21, 2011
I want to import data from excel and move that to Datatable in VB.NET 2008. I wrote and working but its taking too long time.. for 1500 records its taking 4.40 min.
[Code]...
View 1 Replies
Nov 5, 2009
Which one is more faster between DataTable and SqlDataReader while I'm trying to fill Data into FlexGrid with VB.NET?
View 3 Replies
Apr 29, 2010
Is there a way in vb.net to create a sub/function that will take as an argument some kind of pointer to another function, and allow this new sub/function to execute the passed function? What I have are 10-12 xml-rpc functions I am calling against a remote server. Each of these functions has different argument lists (one takes 1 string, another might take 3 strings and one int, etc). All of them return an object.
[Code]...
View 4 Replies
