Decreasing Number Of Records.. Is It Important?

Jul 20, 2005

hi,

If we consider the following scenerio:
a school with 500 students.
On average student takes [x] courses per year, and [y] tests per course.

table of grades:
+------------+-----------+---------+-------+
| student_id | course_id | term_id | grade |
+------------+-----------+---------+-------+
| | | | |
+------------+-----------+---------+-------+
| | | | |
+------------+-----------+---------+-------+

as average, x = 20, y =6 then
number of records = 500 * 20 * 6 = 120,000 record per year.

is this design correct considering number of records generated per year?
Is it important to reduce the "number of records" or no?

thank you.

View 1 Replies


ADVERTISEMENT

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related

Decreasing Tempdb

Nov 27, 2001

how does one make tempdb smaller, when i try to reduce the allocated size it wont let me? it fell over during a process i have tried to srhink the database in enterprise and tried dbcc shrinkdatabase in analyser but to no avail!

View 1 Replies View Related

Page Number && Records Number

Jun 20, 2006

1. how to show page number & total page number in report body?

2. how to show total records number?

View 25 Replies View Related

Decreasing Database Size

Sep 12, 2005

I have a large database currently using up 5GB of space --
I deleted some records of a table and the free space is increased.

So the question is, if I delete some of the data from this table
will the database size decrease? Is there anything else I can do to
reduce the size of the database?
Thank you so very much for any help with this!


Regards Mr. Mumin
Thanks All,

View 5 Replies View Related

Decreasing The Varchar Column Sizes In A Table

Jul 27, 2004

I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

Thanks.

View 2 Replies View Related

Sp_fetch Executed A Lot Of Times, Decreasing Performance - ASP + SQL 2005

May 23, 2008

Hey,

In a ASP application, which used Access and is now being migrated to SQL 2005, I have a medium-sized query with many inner joins. I optimized the tables, with indexes, etc, and if I run the query on the SSMS it returns me like 55 lines in less then a second. On the other hand, when the ASP page passes the query to the SQL 2005, it takes 10 sec. to get the data. Using the Profiler I found that the sp_fetch is being executed a lot of times. It looks like the query is decomposed in smaller pieces and the rows as selected one by one. I'm using OleDb.

How to make the ADO (the culprit in my opinion) to execute that query at once?

Thank you.

View 3 Replies View Related

OLEDB Destination Performance Decreasing Drastically Over Time For Large Input Files

Jul 4, 2006

Hi,

We are processing 60,00, 000 rows(2 GB file) available in a flat file and loading them in to a database tables using OLEDB Destination components. In the data pipeline of an SSIS package we have 1 flat file source reader, 7 look up components(full cache mode), 1 multicast component and 2 OLE DB destinations with fast load option.

We have observed that first 10,00, 000 rows are processed and loaded in to target tables in just 4 minutes time. The second set of 10,00, 000 rows are processed in 15 minutes time. After this for processing each 1,00,000 rows SSIS is taking approximately 8 - 10 minutes time. We are not able to identify the reasons for the unexpected behaviour of SSIS.

We thought that as the input file size is 2 GB SSIS is not able to manage and slowing down over time of execution. We did split the big input file in to 60 small 37 MB (approx) size files. Then we modified the package by adding For-Each loop task to process all the 60 small files and load them in to database server sequentially. Even in this approach also we have identified data loading has slowed down drastically after processing 13 files.

In order to verify is there any problem with reading source file or transformation, we have replaced OLEDB destinations component with Flat File destinations. With Flat file destination the time taken for processing rows is very constant. For every 8 minutes package is able to process 10,00,000 rows and write them in to the destination files. So, there is no problem with the with either Look up components or flat file source reader.

We are sure that target database server is in same state/condition from the starting to the end of package execution. The client box in which we are running the package is having 1 GB RAM. During package execution time the CPU usage is at 30 % and PF usage is 580 MB. SP1 is also installed on both Client and Server.

Does any one have clue what is causing slow down of data load over the time of package execution?

View 3 Replies View Related

Specify The Number Of Records

Apr 26, 2001

Hi,
I want to the users to specify the number of TOPn records to be returned by the query.It has to be a query and not a stored proceedure.
Thanx

View 3 Replies View Related

How To Get Each Number Of Records...

Dec 2, 2006

HI EVERYBODY
This is my procedure
"
CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS

SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc
GO
"
I exec procedure and it returns 12 results with diffirents ID
and want to get these values 1,2,3....12
How do I get these...
I am a beginner.
Thanks for help..

View 6 Replies View Related

Get TOP Number Of Records

May 29, 2015

I'm using SQLCE. I'm using this sql statement:

sSQL = "Select Top (5) fldStatus,
Count(fldStatus) As CountOfSummaryItem From tbl_Records_DR GROUP BY
fldStatus;"

It gets the top 5 of fldStatus but I need the top 5 of CountOfSummaryItem.

View 2 Replies View Related

Number Of Affected Records

Feb 12, 2007

Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005
--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming  that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?
-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?
cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int));
cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue;
Please help me out with this.Thanks

View 14 Replies View Related

How To Count Number Of Records

Feb 26, 2008

hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.

View 6 Replies View Related

Count Number Of Records

May 13, 2008

Using linq what would be the most efficient method of counting the number of users in the users table? 

View 2 Replies View Related

How Can I Now The RecordSource Number Of Records?

Dec 16, 2005

Hi...
I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0...
cheers.

View 1 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 2 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 1 Replies View Related

Getting Number Of Records Modified

Oct 20, 2004

Hi all

My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?

please some one guide me how to do that?

Your help is highly appreciated

View 8 Replies View Related

Selecting A Certain Number Of Records?

Apr 16, 2008

Hi,

Here is a basic example of the issue I am having:

Table 1 columns - name, address, zipcode, favorite food

For table 2 I want to find how many zipcodes exists and also take 20% of the count

Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct

For example:

zipcode ct perc_ct
83746 10 2
93847 20 4

I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1.

Is this possible?

View 1 Replies View Related

My Number Of Records Are Different From Views

Apr 20, 2008

Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view.

my code in my program: this code show all the records i know

If iConn.State = ConnectionState.Open Then iConn.Close()
iConn.Open()
Dim rsBills As New Data.DataSet
rsBills = New DataSet
Dim daBills As New SqlDataAdapter
daBills = New SqlDataAdapter
rsBills.Clear()
Dim cmBills As New SqlCommand
cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ",
cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "

daBills.SelectCommand = cmBills
rsBills.AcceptChanges()
rsBills.Clear()
daBills.Fill(rsBills, "Bills")
Me.DataGridView1.DataSource = Nothing
Me.DataGridView1.DataSource = rsBills.Tables("Bills")

my code in views:

SELECT OR_no, Billing_mo, Account_no, Name, Address, Cno, Pres_read, Mprev_read, Sub_read, Pres2, Prev2, SRead2, Mtr_cons, Amount, NPC, Dmem,
Cmem, Tot_bill, D_Pmnt, Class, Newbill, Prd_fr, Prd_to, Type_Pmnt, Type_Date, Type_Docs, wtax
FROM dbo.Billing


hope you can help me guys

View 1 Replies View Related

Limit On Number Of Records

Apr 21, 2008

is there a limitations of number of records that shows in the table when you use show data table?

View 1 Replies View Related

Sum Up An Unknown Number Of Records

Mar 19, 2007

With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value.
If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter
DECLARE@WantedValue INT

SET@WantedValue = 349

-- Stage the source data
DECLARE@Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)

-- Aggregate the source data
INSERT@Data
(
MaxItems,
FaceValue
)
SELECTCOUNT(*),
Qty
FROM(
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 250 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 90 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 350 UNION ALL
SELECT 450 UNION ALL
SELECT 450 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 1
) AS d
GROUP BYQty
ORDER BYQty DESC

-- Declare some control variables
DECLARE@CurrentSum INT,
@BestUnder INT,
@BestOver INT,
@RecID INT

-- If productsum is less than or equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue
BEGIN
SELECTMaxItems AS Items,
FaceValue
FROM@Data

RETURN
END

-- Delete all unworkable FaceValues
DELETE
FROM@Data
WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)

-- Update MaxItems to a proper value
UPDATE@Data
SETMaxItems =CASE
WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
ELSE MaxItems
END

-- Update BestOver to a proper value
UPDATE@Data
SETBestOver = MaxItems

-- Initialize the control mechanism
SELECT@RecID = MIN(RecID),
@BestUnder = 0,
@BestOver = SUM(BestOver * FaceValue)
FROM@Data

-- Do the loop!
WHILE @RecID IS NOT NULL
BEGIN
-- Reset all "bits" not incremented
UPDATE@Data
SETCurrentItems = 0
WHERERecID < @RecID

-- Increment the current "bit"
UPDATE@Data
SETCurrentItems = CurrentItems + 1
WHERERecID = @RecID

-- Get the current sum
SELECT@CurrentSum = SUM(CurrentItems * FaceValue)
FROM@Data
WHERECurrentItems > 0

-- Stop here if the current sum is equal to the sum we want
IF @CurrentSum = @WantedValue
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
BEGIN
UPDATE@Data
SETBestUnder = CurrentItems

SET@BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
BEGIN
UPDATE@Data
SETBestOver = CurrentItems

SET@BestOver = @CurrentSum
END

-- Find the next proper "bit" to increment
SELECT@RecID = MIN(RecID)
FROM@Data
WHERECurrentItems < MaxItems
END

-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @WantedValue - @BestUnder < @BestOver - @WantedValue
-- If BestUnder is closer to the sum we want, choose that
SELECTBestUnder AS Items,
FaceValue
FROM@Data
WHEREBestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECTBestOver AS Items,
FaceValue
FROM@Data
WHEREBestOver > 0
ELSE
-- We have an exact match
SELECTCurrentItems AS Items,
FaceValue
FROM@Data
WHERECurrentItems > 0With references to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505


Peter Larsson
Helsingborg, Sweden

View 3 Replies View Related

DISTINCT And Number Of Records

Mar 19, 2008

Wondering if there is a way to do this...

I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..

is there any way to do this with SQL?


SELECT DISTINCT user_Name FROM table ORDER BY user_Name


is what I have now....


need something that will return:

user_Name numRecs
bob 3
fred 6
sam 1
linda 2

View 1 Replies View Related

Getting The Number Of Records With Like Values

Jun 14, 2006

I have a resultset that looks something like this:

Anzahl users_statdata_hobbies
---------------------
499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere

I get this with this query which might be altered somehow:



SELECT COUNT(*) AS Anzahl, users_statdata_hobbies

FROM vgetAuswertung2

GROUP BY users_statdata_hobbies

ORDER BY users_statdata_hobbies

Of course this is not normalized but I can't change this.

Nevertheless I need to get the full number of each Hobby and not only the combination of them.

So instead or in addition to the existing recordset I need e.g

357 Essen which ist the sum of all records containing 'Essen' in the above example

The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.

Any ideas someone?

View 4 Replies View Related

Restricting Number Of Records

May 19, 2006

I want to restrict the number of records coming from an OLEDB source. I have 500 records in my source table and I want to process one record at a time

I have I set the MaxBufferRows parameter to 1and it l sends 8 records from OLEDB source

Any help is appreciated.

View 9 Replies View Related

How Do I Put Records Number In Union

Dec 21, 2007



Hi i have sql statement like this :

SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_a
UNION
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_b

the problem is the rec_num repeat for each statement like this :




rec_num empname

1 john
2 maggy
1 lee
2 mary
3 louis

How do i make the rec_num continue for the next statement after union.

View 1 Replies View Related

Sequence Number For Records

Nov 15, 2007

Hi...

I have Sql statement more like this

SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a


rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH



How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :

rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH

View 4 Replies View Related

Number Of Records In SQLDataSource/GridView

Aug 21, 2006

What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!

View 4 Replies View Related

Search For Records That Begins With A Number (0-9)?

Aug 2, 2007

Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny. 

View 6 Replies View Related

Obtaining The Number Of Records In A SqlDataReader

Feb 26, 2004

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?

View 8 Replies View Related

Returning Limited Number Of Records!

Jul 8, 2004

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.

View 2 Replies View Related

Limit Recordset To X Number Of Records

Nov 14, 2000

How do you limit the number of records returned in a recordset? I only want the 10 most recent and I've got a Date column in my database.

View 1 Replies View Related

Updating Records With A Incremental Number

Jun 21, 2000

I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks.

declare @NextKey int
SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS
WHERE TABLE_NAME = "tblEmp"
set @NextKey = @NextKey + 1

DECLARE tblNewEmp_cursor CURSOR FOR
select emp_pk from tblNewEmp

open tblNewEmp_cursor
FETCH NEXT FROM tblNewEmp_cursor
WHILE @@FETCH_STATUS = 0
begin
update tblNewEmp
set emp_pk = @NextKey
set @NextKey = @NextKey + 1
FETCH NEXT FROM tblNewEmp_cursor
end

close tblNewEmp_cursor

View 1 Replies View Related







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