Getting Specific Records: Architecture Question

Sep 6, 2007

This is out of my league. I’m hoping to get some good advice from someone experienced in the area. My inquiry is how to best handle large amounts of records, say 500,000 records or so. I am web programming and can’t send all this info from server to client.

Part of the problem is the manner in which the data gets stored. I cannot calculate what records I need to get for a distant page (i.e. if 10 items per page then where is my data getting page #512).

These are the very first five (5) records.  First row is the primary key.








14
451
0
V5
2 vials
1
V5
8/10/2007
3

20
451
0
V10
2 vials
2
V5
8/10/2007
3

25
451
0
V5
2 vials
1
V5
8/15/2007
3

26
451
0
V10
2 vials
2
V5
8/15/2007
3

27
451
0
V40
2 vials
8
V5
8/15/2007
3
Because records 1 through thirteen had been deleted, the primary keys for the first (5) are no longer 1, 2, 3, 4, 5. Had this been the case, a person could easily retrieve page 512, by mathematical calculation.

Page 1 would have been Records 1..10
# 2 = > 11..20
# 512=> 5111..5120.

I already have a program that loads the entirety into an arraylist; then picks out the page of data from the arraylist location. I could rewrite things so that a temporary SQL Table is created – but I don’t know is that a good idea?

All advice welcome - TIA

View 4 Replies


ADVERTISEMENT

Count Of Specific Records

Jan 30, 2004

I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?

Thanks!
Justine

View 4 Replies View Related

How To Restore Specific Records

Jul 23, 2004

Scenerio:
Its 3pm and a user comes to me and says, she's deleted an invoice with many associated items. I know the affected tables (foreign keys) and I have last nights backup of the db. However I don't want restore the entire db back from last night just the deleted invoice record/s. What is the best practice procedure for accomplishing this?

View 2 Replies View Related

Getting All Records By Specific Year

Jan 24, 2007

I have a column session_date that holds the date in the format: m/d/yyyy or m/dd/yyyy

how do I write my select statement to grab all records by a specific year?

SELECT *
FROM mytable
WHERE session_date =

Thanks for any help.

View 2 Replies View Related

Run Report With Specific Records.

Dec 26, 2006

I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes...

I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that...

if anyone has any ideas please reply...

Thanks,
WoFe

EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9
they would run the report on records: 2, 5, 6, 9

View 1 Replies View Related

Retrieve Specific Number Of Records

Mar 5, 2008

Hi,
How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links.
Table
-----
Name Age
-----------------
Raja 23
Kumar 26
Suresh 30
Rani 22
Subha 32
Ganesh 25

The result will be
Name Age
-----------------
Suresh 30
Rani 22
Subha 32

Thanx.

GANESAN MURUGESAN

View 2 Replies View Related

Select Specific Number Of Records

Jul 20, 2005

Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."

View 3 Replies View Related

Using Min Or Group By To Return Specific Records

Mar 12, 2008



I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right

any help would be appreciated

eg in the following
secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).










secsyscode
securityCode
securitytypecode
securityorder

1
Special
1
2

2
Total Fund
999
17

3
PerfInd
995
14

3
PerformanceIndex
999
17

4556
93152
1
2

4556
10815-0
4
1

4557
558372
1
2

4557
12137-0
4
1

4558
656113
1
2

4558
13154-0
4
1

4559
53673
1
2

4559
13672-0
4
1


I only want the following records to be returned.










secsyscode
securityCode
securitytypecode
securityorder

1
Special
1
2

2
Total Fund
999
17

3
PerfInd
995
14

4556
10815-0
4
1

4557
12137-0
4
1

4558
13154-0
4
1

4559
13672-0
4
1

View 5 Replies View Related

Updating A Specific Amount Of Records In A Table

Jun 20, 2007

I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.

Thanks,

View 4 Replies View Related

HOW TO SEND SPECIFIC RECORDS IN DATA FLOW

Apr 21, 2008



hello, i have 100,000 records in my table. Out of that what means to me is only 500 records that I want to pass thru and send it to destination.

The data I am trying to transport has a column ID with Not Null values in it.

Please let me know how can we do it,

Can we use a look up for ID from the other tables to just pass thru when it finds an ID match,

Am i correct.

View 3 Replies View Related

Write A Query To Group Records Based On Speed (specific Value Of Zero)?

Jun 28, 2012

I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:

Table - Vehicle_Event

Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3

[code].....

OUTPUT_Required:

Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0

[Code] .....

I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.

View 6 Replies View Related

SQL Server 2012 :: Evaluating Rows And Selecting Specific Records

Mar 18, 2015

I have a table with some examples below. I need to identify records where:

1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.

PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely

[code]....

View 6 Replies View Related

Problem: Mssql Locks Several Records In A Specific Table, While No One Is Editing Them

Jul 20, 2005

Hi,I have an Access application with linked tables via ODBC to MSSQLserver 2000.Having a weird problem, probably something i've done while not beingaware of (kinda newbie).the last 20 records (and growing)of a specific table are locked - cantchange them - ("another user is editing these records ... ").I know for a fact that no one is editing records and yet no user canedit these last records in the MDB - including the administrator -while able to add new records.Administrator able to edit records in the ADP (mssql server) where thetables are stored.Please help, the application is renedred inert .Thanks for reading,Oren.

View 3 Replies View Related

Transact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?

Jun 10, 2015

I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.

Givens: All SQL Server 2008 or 2012 tools at your disposal.

Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.

-- A player of our game

-- Table greater than 10 million rows

CREATE
TABLE [dbo].[User]
(
[UserId]              
[bigint] NOT
NULL  
,[country]             
[int] NULL   
-- User’s home country
,[name]                
[nvarchar](15)
NULL  -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]    
[int] NULL
)
-- 0 == free, 1 == paid, for instance

Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.

Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation). 

For instance, if the sample data were:

-- example of [Events.UserLogon] data  -- not the query output we want

EventId     UserId               TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1           1               1000             2010-10-16 00:19:56.460
2           1               1500             2010-10-16 00:20:56.460
3           1               3000             2010-10-16 00:21:56.460
4           1               10000            2010-10-16 00:29:56.460

Event 1 is okay because there’s nothing to compare it against

Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute

Event 3 should be flagged, as the value grew 1500 in a minute

Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)

Query Output (your query should return data in a format like this):

User      Flagged Logon Time    Rate Since Last Logon (money/minute)
John      2010-10-16 00:21:56   1500
Dave      2010-10-16 00:30:50   3200
Bill      2010-10-16 00:35:23   1000

It is likely that you will need to create sample data for both the User and [Events.Logon] tables.  We are looking for a single query that returns data like what is represented in Query Output.

View 3 Replies View Related

SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date

Mar 5, 2015

I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.

Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.

View 2 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?

Jun 30, 2015

We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.

Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?

View 2 Replies View Related

Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?

Oct 10, 2007

I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.

I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.

For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?

View 4 Replies View Related

Command Line Printing To Specific Printers, And Specific Trays

Jan 23, 2007

Hi All,

Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.

Any suggestions is appreciated

Thanks A lot in advance

e,g :

http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render

View 1 Replies View Related

How To Select A Specific Value From Dataset To Fill A Specific Cell ?

Mar 27, 2007

Hi there !

Thanks for taking the time to read this thread.

I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.

My situation is this...

I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-


Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658

I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.

so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)

TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")

Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")


Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")


I only expect this report to print out one page holding the previous values.

However, it ended up printing like this

----------------------------------------------------------

Table1
Column1
DetailRow1 1
DetailRow2

Column1
DetailRow1
DetailRow2 2000


Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658

------------------------------------------------------

I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.

Is this the correct expression ?

=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")

and it give me an error
The value expression for the textbox textbox5 contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Appreciate any advice or suggestion for this scenario !

Thanks!

Bernard

View 3 Replies View Related

Assign Specific Data To Specific Users

Mar 21, 2008

I am very early on in developing a website to track issues with projects which is tied to a SQL database.  I have my Projects Table, my Users Table, and am creating a third table to track issues.  I'm wondering what is the best way to assign specific users to specific data/projects.  The user should only be able to view & update the projects assigned to him.  He should not be able to see other projects.  What is the best way to assign projects/data to the users to make sure they are only viewing their data?

View 1 Replies View Related

Architecture

Sep 6, 2004

Hello,
I want to kow if the following architecture is good :

Disque 1&2 ( Raid 1)
c: OS
d: sqlserver + system tables + log files

Disque 3&4&5 (Raid 5)
e: data

View 1 Replies View Related

Architecture

Sep 29, 2004

Hi,
Someone can tell me if the following architecture is good :

f:master db

g:soft db + log

View 2 Replies View Related

Architecture

Dec 18, 2007

where can we get to know the architecture of sqlserver 2005

View 4 Replies View Related

Database Architecture

Jan 22, 2001

We have database thats transaction intensive, so we are trying to sepetrare ldf file from mdf file to a different disk array. what raid should I use for the Transactional log file(.ldf).

Thank You,
John

View 1 Replies View Related

SQL Database Architecture

Apr 2, 1999

I would like to know where I can find a senior database architect. Someone who can develop
and implement the database and its stored procedures. I am looking for an experienced person.

It is a contract position, in San Franicisco. The pay is good. Could anyone help me?
I tried Dice, Monster and it seems all of you are working...

View 2 Replies View Related

SQL Server Architecture

Jun 2, 2004

I am replacing the corporate SQL Server at work. The new server will have 6 striped disks of 160G with about 4G of RAM. The current SQL Server currently has two instances which run web applications and a small database warehouse about 6G. Analysis Services is also installed.

Due to a couple of new apps being added to the server and the SQL Server 2000 enterprise license we acquired, i was thinking of adding 2 more instances so that the applications can be independently managed in terms of restarting the SQL Server. I also would like to permanently fix the memory settings on each instance to give more resources to more important applications. The log and data files would also be spilt onto 2 separate hard disks. i understand there are implications on performance such as CPU etc. Is it normally advisable to have more than 1 or 2 instances ? Most of the applications are not very CPU intensive. What other implications or performance issues would l have ?

View 4 Replies View Related

Need Recomendations On Architecture.

Sep 1, 2006

We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.

I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.

What is everyone's opinion?

Replication only, for both OLAP reporting and failover?
Mirroring to one database for failover, with replication to a another database for OLAP reporting?

View 3 Replies View Related

SQL Server Architecture

Feb 21, 2004

Hello, Everyone:

What duties are included within SQL Server Architecture? Thanks a lot.

ZYT

View 1 Replies View Related

SQL Server Architecture

May 13, 2008

Can any one of you please explain the SQL architecture in a short..
Or can you give me the link, so i can refer it..
Its very urgent..
Pls help me..

Thanks In Advance
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 4 Replies View Related

SQL Server Architecture HELP

Jun 14, 2007

We are designing a SQL Server architecture that will need to handle a lot of inserts and many users.

We are not sure what approach to take if we should build a distributed server architecture with a Single Master SQL and many Slave SQL Servers.

Or if we should get a really powerful machine with many processers to handle all the connections.

We project we will have about 5,000 Inserts per sec coming from 500,000 users.

What do you think or what has your previous experience been with handling many inserts and few reads for many users?

Thank you

View 2 Replies View Related

Use Of TRY/CATCH: Architecture

Oct 20, 2007

Hi there,
My question is more "architectural" than technical.

One of our standard is to systematically enclose the code of our stored proc within a TRY/CATCH block.

What is your point of view?
Should a TRY/CATCH be included even for simple operations?
Are there any drawbacks?

Thanks!

EDIT: Thanks for your input.

View 4 Replies View Related







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