Row Retrieval Problem In Small Table

Jan 18, 2005

Dear Participant,

I face following problem by last few day, please help me for the same

My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.

I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.

Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.

I don’t understand what is the problem.

Thanks

R.Mall

View 6 Replies


ADVERTISEMENT

Default Retrieval From Table Loaded By DTS

Jul 20, 2005

[MS SQL Server 7]We load a table from a text file using Data Transformation Services. Thesource file is already sorted by primary key order.After the DTS load, the default retrieval order on the target table (select* from targettable) appears to be random. I know that theoretically theretrieval order from a SELECT statement isn't guaranteed, but this is thefirst time that I've actually had a default retrieval not follow theprimary key order in the table. I'm thinking that what we're seeing is thephysical storage order of the rows in the table, which have been somewhatscrambled by the way that DTS loads a text file (probably some I/Ooptimization).Is there any way that we can get DTS to load the table in the order thatthe rows appear in the text file (assuming that's what our problem is)?The Project Lead really doesn't like the fact that the default retrievalorder isn't following the primary key.Regards,Lyle H. Gray

View 1 Replies View Related

Data Retrieval For Reports From Summary Table.

Feb 22, 2007

Hi,
I have a summary table like this






 
Field1
Field2
Field3
Field4
Field5


AAA11
value1
value2
value3
value4
value5


AAB23
value6
value7
value8
value9
value10


BCD14
value11
value12
value13
value14
value15


GFD12
value16
value17
value18
value19
value20


SDL25
value21
value22
value23
value24
value25


AUD56
value26
value27
value28
value29
value30


BER11
value31
value32
value33
value34
value35









Columns are obviously fixed, but not rows.
I want to show this data using lables and SqlDataReader for report purpose like;
Label1.text=dr("value16").toString( )
Label2.text=dr("value28").toString( )
Label3.text=dr("value31").toString( )  etc
 
 Do you have any idea how i can do it or am I approaching it in the wrong way????
 
Thanks.
Michelle
 

View 1 Replies View Related

Retrieval Of The Description Of A Field In A SQL Server 2000 Based Table

Jun 1, 2006

I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.

I have an example table with the following structure:

Table Name: Autioneer's (translated from Portuguese)

'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code

'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name

'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number

I need a way to programatically extract the Fields Description Property from the table

example sintaxe:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'

Thanks for any help that can be offered here
Neil Ramkeerat.

Replies can be sent to neilramkeerat@hotmail.com

View 3 Replies View Related

How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?

Dec 25, 2006

I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

View 4 Replies View Related

SQL 2012 :: Single Column In Staging Table Deems Retrieval Query Unresponsive

Jul 17, 2015

where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.

View 3 Replies View Related

Join Small Table To Big Table Or Vice Versa, Does It Matter?

Jul 23, 2005

If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you

View 3 Replies View Related

Please Help With Small Table Design

Mar 10, 2004

Hello, could someone pls help me with this table design.

I have a project table and a code table. The code table has things like priorities (High, Medium, Low).

Now, I want all projects to be able to use these 'global' codes as well as define their own. So, they could define their own priority code 'Critical', that only their project can see.

Any help would be great.

View 1 Replies View Related

Very Small Table Incredibly Slow

Jul 23, 2005

Hello.I am administering a SQL Server (Enterprise Edition on Windows 2003)from some month and can't understand what is going on in the latestweek (when the db grow a lot).The DB is around 250G, and has one table with 1 billion rows. It isperforming in a decent way, but can't understand why a particolar tablehas strong performance problem.I have a stored procedure that read table from table A and insert them,after processing in table B, and then move them in other table (similarto a Star Schema) for reporting.Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.What is very strange is that performance of table B is really slow. IfI do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.To me it doesn't look like a lock problem, because it is slow also whenthe only procedure that access that table are stopped. I did an updatestatistics with fullscan on this table with no improvement.The DB is on a Storage Area Network that should perform decently. TheLUN I use is configured to use a piece of 32 disk that are used also byother application. I don't have performance data of the SAN. Themachine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,AWE and PAE and 5G reserved for SQL Server.I don't know what to do to solve this situation. Could it be a"corruption problem" that slow this table so much? is it possible thefact the db grow a lot in the last week created problem also to thissmall and simple table?Do you have any idea or hint on how to manage this situation, orpointer to documentation that can help in analizing this situation?For the ones that arrived till here, thank you for your time andpatience reading my bad english...Best Regards,MamoPSI can't rewrite the stored procedure, because it is part of a closedsource product.

View 16 Replies View Related

Insert Small Time Into SQL Server 2000 Table

Nov 14, 2003

Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.

The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:

12:30 PM

It will be stored as:

15/11/2003 12:30:00 PM

I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.


How can i correct this?

View 2 Replies View Related

Slow Performance With A Simple Query In A Small Table?

Jul 9, 2001

In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------

Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0

Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.

View 4 Replies View Related

Not Able To Retrieve Rows From Small Table-So Many Time For So Long

Mar 8, 2005

Dear Participants,

We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-

Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-

Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go

Code validation from here only.

Right validation from here only.

Report retrival validation from here only.

It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.

It was working prior fine but rightnow get problem for while-

Today Dated 08-March this table not accessible fro three times in eight hours-

1st time for 10 minute.

2nd time 10 minutes

3rd time 52 minutes.

Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.

We had have do following by yesterday-

Drop table xxxxmast.

Create table xxxxmast.

Insert required data.


This is realy trouble for our application.

Any help realy great for us.

Thanks

R.Mall

View 3 Replies View Related

SQL Server 2008 :: Small Recursive Numbers Table

Sep 20, 2015

I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."

DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end

View 5 Replies View Related

Table Size Definition - Small/medium/large

Jul 18, 2006

Hi,

Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)

Thanks for your help! :-)

Stu

View 3 Replies View Related

Table Data Retrieval And Optimization Optimization Help

Apr 10, 2008

Hello Everybody,

I have a small tricky problem here...need help of all you experts.

Let me explain in detail. I have three tables

1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.

There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.

If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.

The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.

Please help me to optimize the query and data retrieval time from Team table.


Thanks,
Ganesh

View 4 Replies View Related

Analysis :: SSAS Tabular Small Database Fails In Excel Pivot Table Because Of Memory Shortage?

May 7, 2015

I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.

What might be the issue in such a small database? I would understand in a big database, but not on this one.

View 13 Replies View Related

Data Retrieval

Jan 7, 2008

i m having a huge problem! how to retrieve data from notepad files using asp.net and store the info in fields in MS-Access/Sql db? Plz help !!

View 1 Replies View Related

Previous Value Retrieval

Jul 10, 2006

I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?TIA

View 4 Replies View Related

SQL Insertion - Primary Key Retrieval

Apr 12, 2007

Hi there,
Is there a way to retrieve the set of primary keys from an inserted record, especially when the primary keys are generated upon insertion (ex. identity, uniqueid)?  Basically I need to get the ID of the record I just inserted so that I can use it as a reference to that record in other places.
I'm using MS SQL and raw SQL commands (ie. no database interfacing objects).  I'm hoping I can tag some sort of SELECT statement on the end of the INSERT command that will give me the results that I need.
Thanks!

View 3 Replies View Related

SQL Server Data Retrieval

Jul 12, 2004

Hi,



I wanted to retrieve all the databases present in an SQL Server and put them in an Dropdown list.

Then retrieve all tables of the selected database

and finally retrieve all data from the selected table (This i know)

How do i accomplish the above two.


Regards
Vijay R

View 3 Replies View Related

Sql Image Upload And Retrieval?

Apr 9, 2006

Hi all, I dont know if anyone here can help me...I am using ASP.NEt with VB.Net and SQL Sever...What I am trying to do is create a system whereby it allows users to upload images into sql server, and thereon later retireve this image.I used a book called The Ultimate VB.Net and ASP.Net Code Book, and it basically gave me the code below to upload and retrieve images.  I seem to have managed to allow the uploading of images, with them being stored as <byte> in my database, thought I have no idea how to retrieve them...Does anyone have any ideas???Here is the code I followed.. :"Storing Uploaded Files in Your Database First, a few tips on storing files inside your SQL Server database.For convenience, you’ll really need to store at least three bits of information about your file to get it out in the same shape as you put it in. I’d suggest “dataâ€? (a field that will hold your actual file as a byte array, data type “imageâ€?), “typeâ€? (a field to hold details of the type of file it is, data type “varcharâ€?), and “lengthâ€? (a field to hold the length in bytes of your file, data type “intâ€?). I’d also recommend “downloadNameâ€?, a field to hold the name that the file had when it was uploaded, data type “varcharâ€?. This helps suggest a name should the file be downloaded again via the Web.The problem you have is translating the information from the File Field control into an acceptable format for your database. For a start, you need to get your file into a byte array to store it in an image field. You also need to extract the file type, length, and the download name. Once you have this, set your fields to these values using regular ADO.NET code.So, how do you get this information? It’s simple: just use the following ready-to-run code snippets, passing in your File Field control as an argument. Each function will return just the information you want to feed straight into your database, from a byte array for the image field to a string for the file type.Public Function GetByteArrayFromFileField( _     ByVal FileField As System.Web.UI.HtmlControls.HtmlInputFile) _     As Byte()     ' Returns a byte array from the passed     ' file field controls file     Dim intFileLength As Integer, bytData() As Byte     Dim objStream As System.IO.Stream     If FileFieldSelected(FileField) Then         intFileLength = FileField.PostedFile.ContentLength         ReDim bytData(intFileLength)         objStream = FileField.PostedFile.InputStream         objStream.Read(bytData, 0, intFileLength)         Return bytData     End If End Function Public Function FileFieldType(ByVal FileField As _   System.Web.UI.HtmlControls.HtmlInputFile) As String     ' Returns the type of the posted file     If Not FileField.PostedFile Is Nothing Then _       Return FileField.PostedFile.ContentType End Function Public Function FileFieldLength(ByVal FileField As _   System.Web.UI.HtmlControls.HtmlInputFile) As Integer     ' Returns the length of the posted file     If Not FileField.PostedFile Is Nothing Then _       Return FileField.PostedFile.ContentLength End Function Public Function FileFieldFilename(ByVal FileField As _   System.Web.UI.HtmlControls.HtmlInputFile) As String     ' Returns the core filename of the posted file     If Not FileField.PostedFile Is Nothing Then _       Return Replace(FileField.PostedFile.FileName, _       StrReverse(Mid(StrReverse(FileField.PostedFile.FileName), _       InStr(1, StrReverse(FileField.PostedFile.FileName), ""))), "") End Function Sorted! One question remains, however. Once you’ve got a file inside a database, how do you serve it back up to a user? First, get the data back out of SQL Server using regular ADO.NET code. After that? Well, here’s a handy function that’ll do all the hard work for you. Simply pass the data from your table fields and hey presto:Public Sub DeliverFile(ByVal Page As System.Web.UI.Page, _   ByVal Data() As Byte, ByVal Type As String, _   ByVal Length As Integer, _   Optional ByVal DownloadFileName As String = "")     ' Delivers a file, such as an image or PDF file,     ' back through the Response object     ' Sample usage from within an ASP.NET page:     ' - DeliverFile(Me, bytFile(), strType, intLength, "MyImage.bmp")     With Page.Response         .Clear()         .ContentType = Type         If DownloadFileName <> "" Then             Page.Response.AddHeader("content-disposition", _               "filename=" & DownloadFileName)         End If         .OutputStream.Write(Data, 0, Length)         .End()     End With End Sub Simply pass your byte array, file type, and length, and it’ll send it straight down to your surfer. If it’s an image, it’ll be displayed in the browser window. If it’s a regular file, you’ll be prompted for download.If it’s made available for download, this function also allows you to specify a suggested download file name, a technique that many ASP.NET developers spend weeks trying to figure out. Easy! Working with Uploaded Images Whether you’re building the simplest of photo album Web sites or a fully fledged content management system, the ability to work with uploaded images is a vital one, and with ASP.NET, it’s a real doddle.The following code snippet shows you how, by example. It takes a data stream from the File Field control and converts it into an image object, adding simple error handling should the uploaded file not actually be an image. The code then uses this image object to extract a few core details about the file, from its dimensions to file type:' Get data into image format Dim objStream As System.IO.Stream = _   MyFileField.PostedFile.InputStream Dim objImage As System.Drawing.Image Try     ' Get the image stream     objImage = System.Drawing.Image.FromStream(objStream) Catch     ' This is not an image, exit the method (presuming code is in one!)     Exit Sub End Try ' Filename Dim strOriginalFilename As String = MyFileField.PostedFile.FileName ' Type of image Dim strImageType If objImage.RawFormat.Equals(objImage.RawFormat.Gif) Then     strImageType = "This is a GIF image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Bmp) Then     strImageType = "This is a Bitmap image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Jpeg) Then     strImageType = "This is a JPEG image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Icon) Then     strImageType = "This is an icon file" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Tiff) Then     strImageType = "This is a TIFF file" Else     strImageType = "Other"   End If   ' Dimensions   Dim strDimensions As String   strDimensions = "Width in pixels: " & objImage.Width & _     ", Height in pixels: " & objImage.Height   ' Send raw output to browser   Response.Clear()   Response.Write(strOriginalFilename & "<p>" & strImageType & _     "<p>" & strDimensions)   Response.End() "For some reason the retrieval code isnt working for me..Can anyone provide any help? even if it means using another method?

View 1 Replies View Related

Duplicate Records Retrieval

Jun 23, 2008

I want to remove duplication entries on the basis of compaparing
ApID,StID,CreationDate.If three match only i shoul get result set for deletion


ID ApID StID RoleID CreationDate
40100522008-01-31 20:27:03.850
41101522008-01-31 20:27:03.850
42101522008-01-31 20:27:03.850
90110118122008-01-31 20:27:03.850
90210118122008-01-31 20:27:03.850
90310127422008-01-31 20:27:03.850
30251013422008-02-22 11:43:39.153
90410147422008-01-31 20:27:03.850
30021016422008-02-22 11:28:34.513
317010179422008-02-22 12:35:17.450
9061019422008-01-31 20:27:03.850
43102522008-01-31 20:27:03.850
90710208122008-01-31 20:27:03.850
90810217422008-01-31 20:27:03.850
319410227422008-02-22 12:43:46.030
90910238122008-01-31 20:27:03.850
475010248122008-05-23 14:27:45.317
91210278122008-01-31 20:27:03.850
475110288122008-05-23 14:27:45.317
2638103522008-02-21 16:26:19.877
25711031501282008-02-19 14:23:57.610
32051031422008-02-22 12:47:32.653
91510327422008-01-31 20:27:03.850
310310398122008-02-22 12:08:32.043
2639104522008-02-21 16:26:19.877
91810407422008-01-31 20:27:03.850
9191041422008-01-31 20:27:03.850
32061042422008-02-22 12:47:50.903
92010439422008-01-31 20:27:03.850
9221045422008-01-31 20:27:03.850
32081046422008-02-22 12:48:11.857
9241048422008-01-31 20:27:03.850
32091049422008-02-22 12:48:30.687
2640105522008-02-21 16:26:19.877
92610507422008-01-31 20:27:03.850
31561051522008-02-22 12:27:06.700
92710529422008-01-31 20:27:03.850
92810537422008-01-31 20:27:03.850
309810548122008-02-22 12:07:00.000
30441055522008-02-22 11:50:26.373
9291056422008-01-31 20:27:03.850
93010588122008-01-31 20:27:03.850
93110599422008-01-31 20:27:03.850
44106522008-01-31 20:27:03.850
31611060422008-02-22 12:29:31.687
93310617422008-01-31 20:27:03.850
93410629422008-01-31 20:27:03.850
317410637422008-02-22 12:36:24.170
9351064422008-01-31 20:27:03.850
9361064422008-01-31 20:27:03.850
93810679422008-01-31 20:27:03.850
93910689422008-01-31 20:27:03.850
31101069422008-02-22 12:10:33.467
94010707422008-01-31 20:27:03.850
94110707422008-01-31 20:27:03.850
94210707422008-01-31 20:27:03.850
94310707422008-01-31 20:27:03.850
321910717422008-02-22 12:51:09.687
94410729422008-01-31 20:27:03.850
9451073422008-01-31 20:27:03.850
322110748122008-02-22 12:51:53.560
32221075422008-02-22 12:52:18.373
94610768122008-01-31 20:27:03.850
9471077422008-01-31 20:27:03.850
32231078422008-02-22 12:52:41.717
94810799422008-01-31 20:27:03.850
322010809422008-02-22 12:51:30.467

View 2 Replies View Related

Query Retrieval Is Slow

Nov 23, 2005

Hi,I am using MS Access as the front end and it's using ODBC link to connectto the backend tables residing at SQL SERVER.I have some queries that are doing some calculation and the time taken toretrieve the data has been quite slow.The no of data in that table is around 500K records. and the tables havealready got the PKs defined.How can i improve the performance of the query besides converting to SQLViews or SP(this is going to be diffcult as i am getting some filterparameters from the forms in MS Access) ?Or what further checks shld i be doing to find out the cause. I have donePerformance Monitor and the CPU processing% is around 20 - 55% while runningthose querieskindly advisetks & rdgs--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1

View 9 Replies View Related

Data Retrieval Using IDataReader

Nov 16, 2007

Code Block

string commandString = "SELECT Id,Name FROM [DatabaseTable];";
using (SqlConnection conn = new SqlConnection(connectionString))
{

SqlCommand cmd = new SqlCommand(commandString, conn);
conn.Open();
IDataReader rdr = cmd.ExecuteReader();


IList ids = new List();
IList names = new List();
while (rdr.Read())
{

ids.Add((int)rdr[0]);
names.Add((String)rdr[1]);
}
rdr.Close()
}




What i want to know is if there is any better way of obtaining the 'id' and 'name' data
values then just assuming that id is the first returned object and name is the second returned object in each reader record.

ie. Is there any way to retrieve a specific field from the reader in a Dictionary-type manner...?





Code Block

while(rdr.Read())
{

ids.Add( (int)rdr["Id"] );
names.Add( (String)rdr["Name"] );
}




View 6 Replies View Related

Data Retrieval Terribly Slow

Jul 27, 2006

Hi,
I'm using ASP.NET 1.1, SQL Server 2000 Server:
I  followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created.  For example I've written this Function in a class to return a sqldatareader:
Public Function GetAdvanceSearch(ByVal s As String, ByVal Ext As Integer, ByVal fdate As DateTime, ByVal tdate As DateTime) As SqlDataReader
Dim oDrAdSearch As SqlDataReaderDim oCmdGetSearch As New SqlCommand("spAdvanceSearch", oComConn)
With oCmdGetSearch   .CommandType = CommandType.StoredProcedure   .Parameters.Add(New SqlParameter("@DialNo", SqlDbType.VarChar)).Value = s   .Parameters.Add(New SqlParameter("@FDate", SqlDbType.DateTime)).Value = fdate   .Parameters.Add(New SqlParameter("@TDate", SqlDbType.DateTime)).Value = tdate   .Parameters.Add(New SqlParameter("@Ext", SqlDbType.Int)).Value = ExtEnd With
oComConn.Open()oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection)
If oDrAdSearch.HasRows Then   Return oDrAdSearchElse   Return NothingEnd If
End Function
And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"):
Dim objCalls as New Calls
DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS.......>)DataGrid1.Databind
My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet.
What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this.
Thanks in Advance. Looking forward for a reply from some one.
 

View 3 Replies View Related

Help Regarding Storing And Retrieval Of Files In Sql Server

Oct 29, 2004

Hi,
Thanks in advance.

I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#.
Secondly,Is it possible to search file in BLOB using SQL server Full text search service.

View 1 Replies View Related

Retrieval Of Duplicate Rows In SQL Server 7.0

May 14, 2000

How can i retrieve duplicate rows and show in sql server 7.0.
It is very urgent.
regards,
Srinivas.

View 4 Replies View Related

DATA Retrieval Stored Procedure

Mar 29, 2001

Is there a stored proceduire that grabs all the data from a specified table and places it in a file.

View 1 Replies View Related

Complicated Data Retrieval Routine

May 4, 2006

Hello all,

I'm stumped on how to solve this question so I figured i'd ask the community. As a warning i'm not sure best how to describe my situation so i'll try and give as much detail as I can.

First in table A, I have two columns that already have data in them that are numeric (Col1, Col2). Also, in table A I have two more columns that are going to derive their data based of a complicated data retrieveal routine (Col3, Col4). So my table structure looks something like this:


Code:

Table A
Col1 Col2 Col3 Col4
20 20 NULL NULL

(Where Col3, and Col4 are going to be populated based off the routine)



The Data for Col3 and Col4 is in an excel spreadsheet that i'd like to convert into a table for MSSQL. However i'm not sure how to do this because in the spreadsheet there is a lookup routine (that i'm trying to copy to MSSQL code, i'll show that in a minute) that generates its values based off data in the X / Y columns, so something like this:


Code:

_|1__|2___|3__
0|0 |0 |0
1|1 |15 |25



So when 2 is met, and 1 is met, they would equal '15'. No arithmetic involved, just simply matching up the X / Y and pulling the data.

My question is, how do I create tables out of this, so my lookup routines can get the values as a result of matching X / Y? (2, 1 = 15)???

The excel routine is this:

=IF(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)>F46/12*0.125,F46/12*0.125,(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)))

Thanks!

View 3 Replies View Related

SQL Server Data Retrieval Problem

Apr 11, 2007

Hello,

i am getting a hard time in minimizing time for data retrieval, over SQL Server DataBase. My DataBase consist of 2 tables. One of the table has more than 10000 entries and other table with more than 10 million entries. I have used SQLNative Client for connecting to data base and my goal was to find a value from the 1st table and search it out in 2nd table. The result is more than hundered thousand rows. Now the problem is: the time it took for retrieving those rows is much slower approx. 12 minutes. Can this time be cut down. I have used SQLClient connection to make sure it is accessing SQL server on a direct access base.



Also, i am using

SqlClient::SqlDataReader

for reading, getting rows returned by the my query.



Please help me out.

Thanx.



R. T.

View 4 Replies View Related

Question About Retrieval Of Client Process IDs

Jul 20, 2005

Is there any short way to make the server give out IDs of all clientprocesses connected to a specified DB? I guess this information mustbe stored somewhere in system databases...

View 1 Replies View Related

Question On Data Retrieval Using RDA Pull

Jun 3, 2008

Hi All,


I am working on an application to retrieve data to the windows ce 3.0 handheld from the Sql server database. I am able to retrieve the data from the server using RDA pull method and able to see the data on the local handheld database. To display the retrieved data in the dialog to the user, it requires to query the local databse and get the information and then display the information, which is taking some time.

My question is, to reduce the time and improve the performance , instead of pulling the data to the local table, is there any way to pull the data and have it in memory and display the details to the user?

To develop the above application, I used some of the code to pull the data from the server from the sample application C:Program FilesMicrosoft SQL Server CE 2.0SampleseVCeVCReplRdaHPC. I am developing the application for windows ce 3.0 device using eVC 3.0

thanks
pyd.

View 4 Replies View Related

Profile Image Binary Blob Retrieval

Jan 13, 2008

 
Hi, All
Can you add a group of images into the aspnet_profile table as a serialized binary blob?
If so how do you retrieve the image from the blob?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved