Why Does The Merge Join Edit Control Take So Long To Close At Design Time?
May 14, 2008
When I'm designing a merge join in BIDs, in particular a large one with say a couple hundred columns on the left part of the join (and only 1-2 columns on the right side), it takes 2-3 minutes to close and I notice it pegs the CPU on the computer during that time. I'm developing on a quad dual core 64 bit machine with 32 Gb of RAM.
What makes this problematic is if you have a several merge joins in the pipe, and you do something like add a column. Then you have to re-open every merge join to include that column, waiting 2-3 minutes for it to close. So adding a column to a pipe with 10 merger joins will take 20-30 minutes, most of which is just waiting for edit boxes to close. I've tried the options of "work offline" and setting the ValidateExternalData to false in the merge join property but those don't help. Anyone encounter this problem?
Thanks,
Lee
View 6 Replies
ADVERTISEMENT
Oct 27, 2005
Lets say I am selling parts for cars, trucks, suvs, vans, etc.
I want a user to search for a part by selecting a vehicle type, then a vehicle make, and then a vehicle model.
So
Vehicle Makes have to be related to Vehicle Types (I.E: Land Rover doesn’t make a car) and vehicle models need to be related to vehicle make (I.E Toyato doesn’t make a mustang Ford does).
So I am okay with those relationships individually but what is confusing me is how to ensure that a Mustang doesn’t show up as a Ford Car.
Lets say I have:
vehicle_typevehicle_type_cdvehicle_type_desc
vehicle_makevehicle_make_cdvehicle_make_descvehicle_modelvehicle_model_cdvehicle_model_descThen I might have an intersect between vehicle_type and vehicle_make because certain makes don’t have certain types of vehicles. BMW doesn’t have a truck, Lexus doesn’t have a truck, so when a user selects truck as a make lexus nad BMW shouldn’t show up in the drop down.Vehicle_type_makeVehicle_type_make_keyVehicle_type_cdVehicle_make_cd I might have the same relationship between make and model:
View 10 Replies
View Related
Jun 12, 2008
I know that similar items have been posted before but I have not found a solution to my issue by reading them.
I have a text field which holds amendment details and it cannot be updated through QA. I have 50 records which I have had to manually update through Enterprise Manager. I have been able to edit the amendment details field of 48 of these records. However for 2 records the amendment details field contains <long text> and as we all know, there does not seem to be an easy way to edit these.
Before I give up for good, does anyone have any suggestions?
Help much appreciated.
View 6 Replies
View Related
Jul 20, 2005
Hi Guys,I have editing a SQL Server table field that have long text data. I amupdating some text in this field. How can I update this field insteadof re-write all text. With the Select command its gives me completetext in one line and it hard to read it. Any idea. Thanks in AdvanceAdnan
View 1 Replies
View Related
Aug 25, 2006
I have null values in some of the fields & when I use EDIT to make changes these changes are not recorded + I receive no error.
View 1 Replies
View Related
Dec 15, 2006
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View 1 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Jul 23, 2005
How can i mark a row with its last edit time?With access it was obvious, in sqlserver?
View 1 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
Dec 29, 2007
Hey I was wondering if you
all could help me with my database. I am very noobish as databasing and I am
not sure what I have is good or bad. This is very long since I tell you basically
what my whole site does/will do. This is in hopes that it will give you a
better understanding of how many database will work.
Japanese WebSite
Purpose of the site:
This sites intention is to help people learn the 2 basic
Japanese character sets Hiragana and Katakana.
Hiragana is usually used for Japanese words and contains 46 basic characters,
plus a set of special characters. Katakana is usually used for foreign words
and also contains 46 basic characters plus a special set of characters.
Hiragana and Katakana are written differently when they are written with Japanese
characters. However if you right them in romaji (this is basically writing words
as if you where writing English and does not involve using characters. This is
used for foreigners while they are learning how to write the Japanese
characters).
Hiragana and Katakana are pronounced exactly the same.
This site will use a quiz style format. The user will first choose from the set
of Hiragana characters then the Katakana characters. Once chosen the website
will randomly choose a character and display the Japanese symbol of it. The user will then write the romaji
representation in the text box provided.
It will be then checked
and if the user is right they will get a new symbol. If they are wrong they
will have try again. The purpose for
this is for the user to get use to see how the character looks like but at the
same time writing in romaji what will be easier for them. This should make it
easier for them the next time they see the character to know how to say it.
Version 1
The site will go through many versions. This version I am
focusing on using C#,asp and ms sql. Later on in future versions ajax and
javascrip will be introduced and parts will be changed. Version 1 also uses CSS
and html.
Version 1 will contain these features.
1. A
practice section. A user will go through a wizard like setup that will first
let them choose from the basic 46 Hiragana characters (the other special
characters will be added later). They will then go to the next setup and choose
from the 46 basic Katanana characters. After they go to another section that
asks them how many repetitions they want to go through. If the user is a member(free membership) they
will have the option to save their settings that will be stored and later
available on the front page called quick links. The next page of the wizard is
actually going through the quiz. Once they are done they will see a summary of
how many they got right,wrong and how many they needed assistance on. At this
point they will be able to view a chart to see their progress(current chart
showing there last attempt, a week one
and a month one). These charts will be either made using c# or ms sql(I saw a
something that says you can make something like that but have not confirmed
it). I may do both ways for a learning experience.
2. A Registration form, login and logout.
3. Quick
link. Quick link will be for registered members so they don’t have to go
through the wizard every time to make their practice quiz up. A user will go
through the wizard once and choose what they want and save it. Quick link will be on all the pages in the
form of mostly likely a grid view. A user will be able to store 3-5 different quiz’s
(in the future maybe more). A user can then click on the quick link and it will
just take them to the quiz. The user will also will be able to update their
quiz through the grid view. This will take them to the start of the wizard and
they can go through it again and then just save over it. They will also be able
to delete there quick links through the grid view.
Currently done
Half of step 1 has been completed. Currently a user can
go through the wizard and select what they want and choose how many repetitions
and go through the quiz. However the summary and charts are not done.
Future versions.
Design will be a major part of it since I have not
focused on this at all. Also since I wanted to experiment with dynamic controls
I made all the checkboxes for the selections dynamically. This caused a problem
that a user must hit a save button since the controls have to get recreated and
updated.
I think normally this might not have been a problem but
since I since I am using a multiview(to create a effect that the wizard is all
on the same page and not the whole page has to be reloaded) it caused some
problems(I can’t really remember since it was a couple months ago when I
started but had to stop due to the amount of school work). This is why I want
to go back later and make them with ajax. This also makes another learning
experience.
Where I am at now.
When I started this I really did not think much of my
database mainly because I am not good at data basing and don’t really like it that
much. I have had a course in data basing (was with oracle though)that was part
of my program. At the time I did not understand very much since I found the
teacher going too fast and he started at chapter 8….. Also when you got 7 other
courses with it it’s hard to learn lots.
I have gone through some tutorials and stuff. I am not
very keen on reading a database book since I just don’t have the time since I
want to also read a book on c#, javascript, ajax and so forth. Also I don’t
know how much I will retain/learn from a book. With some of the stuff I just
find its better to try to something where you will be interested in and then
when needed read chapters of a book or ask questions. This is what I done for my c# stuff like I
had a c# with asp course and 2 months after the courses I started this site and
I had to ask myself what the heck did I learn in that course since it seemed I
knew nothing(and I was close to top in my class). With pounding it out and
reading I was able to accomplish what I needed. Like I still want to read a
book on C# but for now it’s just not going to happen same with Data basing.
All the pervious information was so you can understand
what my website is trying to do so you can better evaluate my database design
and answer my questions.
First Design of my
database.
This is when I just made it up on the spot and did not do
any database design or anything.
Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?
I have currently 2 tables How one called Hiragana and the
other Called Katakana
HiraganaID = PK
HiraganaID HiraganaCharacter HiraganaImage
----------- -----------------
--------------------------------------------------
1 a Images/hiragana/a-o/a.jpg
2 i Images/hiragana/a-o/i.jpg
3 u Images/hiragana/a-o/u.jpg
4 e Images/hiragana/a-o/e.jpg
5 o Images/hiragana/a-o/o.jpg
6 ka Images/hiragana/ka-ko/ka.jpg
Katakana
KataKanaID = PK
KatakanaID KatakanaCharacter KatakanaImage
-----------
-------------------------------------------------- --------------------------------------------------
1 chk_Kata_a
Images/katakana/a-o/a.jpg
2 chk_Kata_i
Images/katakana/a-o/i.jpg
3 chk_Kata_u Images/katakana/a-o/u.jpg
4 chk_Kata_e
Images/katakana/a-o/e.jpg
5
chk_Kata_o
Images/katakana/a-o/o.jpg
I quickly found that this design was very bad since I was
unable to add new rows very easily. Each character set has each of its characters
in rows of 5. Some rows however have only 3 romaji characters so what they do
is leave the other cells blank representing that they don’t exist. I wanted to
do this too so I figured the easier way would be to have just null rows of
data. So say for “ya,yu,yo� it would be like this “ya,null,yu,null,o�. That’s
how it would appear in the database so when my c# would read it in it would not
have to account for rows that had less than 5 characters since it would have
already the nulls for it.
You can check a chart out here: http://www.alpha.ac.jp/japanese/img/moji.gif
So with this poor design it would result me in basically
rewriting the database.
I have thought of 2 possible ways to. I am not sure if
either way is good but I will try to explain my reasoning as much as I can.
Possible Way 1
In this approach I have 5 tables.
Hiragana
This table stores all the information about Hiragana
Characters
CREATE TABLE [dbo].[Hiragana](
[HiraganaID] [int] IDENTITY(1,1) NOT NULL,
[HiraganaCharacter] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[HiraganaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Hiragana] PRIMARY
KEY CLUSTERED
Katakana
This table stores all the information about Katakana
Characters
CREATE TABLE [dbo].[Katakana](
[KatakanaID] [int] IDENTITY(1,1) NOT NULL,
[KatakanaCharacter] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[KatakanaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Katakana] PRIMARY
KEY CLUSTERED
Quick Links
This table will store the users quick link information.
CREATE TABLE [dbo].[QuickLinks](
[QuickLinkName] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserID] [int] NOT NULL,
[SavedCharacters] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SavedImagePaths] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Users
This will store login information.
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmailAddress] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Password] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTimeStamp] [smalldatetime] NOT
NULL,
CONSTRAINT [PK_Users] PRIMARY
KEY CLUSTERED
Charts
This will store all information that will be used for
charts.
CREATE TABLE [dbo].[Charts](
[PracticeNum] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Correct] [numeric](3, 0) NOT NULL,
[Wrong] [numeric](3, 0) NOT NULL,
[AssitanceNeeded] [numeric](3, 0) NOT NULL,
[TimeDateStamp] [smalldatetime] NOT
NULL,
CONSTRAINT
[PK_Charts] PRIMARY KEY
CLUSTERED
(
Relationships
Users and QucikLinks have a PK to FK relationship this is
because quicklinks is dependent on the user having a user account. It also away
to identify which QuickLinks will belong to who.
Question 2: Currently I don’t have a primary Key for this
table what would be a good primary key for this table?
QuickLinks also does not have any relationship to either
to HiraganaCharacters or Katakana Characters. This is because I did not feel it
was necessary to grab the information from here. I simply could just grab it
from the array that holds this information from my c# code.
Question 3: How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName). Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?
Option 1
QuickLinkName UserID
SavedCharacters SavedImagePaths
---------------- ----------- ------------------- --------------------------
QuickLink1 1 a image/a.jpg
QuickLink1 1 i image/i.jpg
QuickLink1 1 u image/u.jpg
QuickLink1 1 e image/e.jpg
QuickLink1 1 o image/o.jpg
QuickLink1 2 a image/a.jpg
QuickLink1 2 ya image/ya.jpg
QuickLink1 2 ki image/ki.jpg
QuickLink1 2 yo image/yo.jpg
QuickLink1 2 n image/n.jpg
Option 2
QuickLinkName UserID
SavedCharacters SavedImagePaths
---------------- ----------- ------------------- --------------------------
QuickLink1 1 a,i,u,e,o image/a.jpg, image/i.jpg,
image/u.jpg, image/e.jpg,
image/o.jpg,
QuickLink2 2 a,ya,ki,yo,n image/a.jpg,
image/ya.jpg,
image/ki.jpg, image/yo.jpg,
image/n.jpg,
Charts table also has a Relationship with Users
The thinking for this one is that Charts will use the
UserID to tell who this data belongs too. It will store the number of correct
answers, wrong answers and AssitanceNeed. This table will also hold a timeDateStamp.
The reason for this is because I want to have a chart that displays the last
Quiz they did and also the stats of a week’s worth of quizzes, and month’s
worth of quiz’s. With a timeDateStamp I should be able to add up all those columns
and then display them for whatever period I choose.
Second Possible
Way.
Characters
CREATE TABLE [dbo].[Chracters](
[CharacterID] [int] NOT NULL,
[CharacterName] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[CharacterPath] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric](3, 0) NOT NULL,
CONSTRAINT [PK_Chracters] PRIMARY
KEY CLUSTERED
This way uses 4 charts instead of 5. The main difference
is that the Hiragana Charts and Katakana Charts have been merged into one
table.
This way has another relationship between Characters and
Quick Links. This will store the Character
ID instead of the actual Characters and Path through the use of C# code. When a
user selects a box it will make a note of the CharacterID. When it is time for
the user to use the QuickLink it will join the tables together and filter out
the CharacterIDs. Giving me the CharacterName and Path.
The problem with this way is that well the code I written
will need to be changed since currently it grabs stuff from 2 tables into one.
I don’t know if this would save anytime over the first way either.
I hope these are sort of close to an alright design since
I did try to think about how my website would interact with the database.
Sorry for it being so long.
Summary of Questions
Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?
Question 2: Currently I don’t have a primary Key for this
table what would be a good primary key for this table?
Question 3: How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName). Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?
View 9 Replies
View Related
Nov 7, 2006
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
View 2 Replies
View Related
May 6, 2008
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
View 3 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
May 15, 2008
I'm using a script task to split my control flow before performing different data flow tasks depending upon the result of the script.
The dataflow tasks populate a single dataset with data before entering a foreach loop where the data in the dataset is consumed.
The foreach loop is valid for both dataflow tasks but I can€™t get it to accept multiple inputs.
I could have 2 copies of the foreach loop, one for each branch, but this seems wasteful and also means I have to maintain the same chunk of code in 2 places.
Can I join control flows or is there a better way to achieve this?
Cheers.
View 6 Replies
View Related
May 21, 2008
A few pointers would be appreciated.
I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)
For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table
select dim.id
from SQLServer1.database08.dim_table
union
select dim.id
from SQLServer1.database07.dim_table
union
select dim.id
from SQLServer1.database06.dim_table
Now next year the names of the databases on SQLServer1 will be database09,database08,database07!
Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?
Many thanks,
Ells
p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.
View 2 Replies
View Related
Oct 24, 2006
Hi everyone,
Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again
.
That same behaviour happened with Sql2k-dts.
Thanks in advance and regards,
View 3 Replies
View Related
Feb 4, 2008
I was just wondering what would be a preferred approach in designing the tables against checkboxlist?
For example I have 3 checkboxlist:
chklVendor
chklModel
chklColor
and each will have lets say 4 - 5 list items.
Here are the different types of approaches that I've seen when designing database tables:
1. Creating tables for each checkboxlist.
2. Creating a single table and storing the values in a comma delimited format.
3. Using XML
Also these attributes will be used in the search form as one of the fields.
View 1 Replies
View Related
Oct 28, 2007
Hi,
I am new to SSRS.
I have created simple report using reporting services, In which i am using one List element and inside that 8 text boxes and a table, in which 4 texboxes is used as lable and remain 4 textbos is value from dataset and table displays details as below for each unquie call number with table header marked in red color. When i click preview i get below report(Report 1) for call number 4 and 10 records in table which is under list. But for call number 4 still 6 records as to be display in next page with text boxe element which marked in Green color in Report 1. Now i am getting report with out List element i.e (Report 2 given below )
Report generated for Call numnber 4 (on click of preview ) (Report 1)
Generated Report
Call No
4
CallDate
2/2/2007
Customer Name
ZINDAL STEEL P . LTD
Address
Chennai
SL Number
PartNumber
PartName
Qty
Rate
Amount
1
12345
SCREWS
100
10
1000
2
47555
BOLTS
200
25
5000
3
125453
RIVETS
300
40
12000
4
15786
RATCHETS
500
23
11500
5
15566
TORQUE WRENCH
600
45
27000
10
5456
HARMONIZATION TOOL
700
45
31500
(Report 2)
Report generated for Same Call number 4 in next page with out List element, In this page i want display the same textboes and values which are marked in green color.(This 6 records alos belongs to same call Number 4 )
SL Number
PartNumber
PartName
Qty
Rate
Amount
11
12345
SCREWS 6"
100
10
1000
12
47555
BOLTS 5"
200
25
5000
13
125453
RIVETS 5"
300
40
12000
14
15786
RATCHETS Big
500
23
11500
15
15566
TORQUE WRENCH Small
600
45
27000
16
5456
HARMONIZATION TOOL
700
45
31500
Can any one please guide me how to do this kind of desing.
Thanks and regards
Manju
View 2 Replies
View Related
Jun 13, 2006
hi all of you,
I haven€™t idea if the following description is a issue or not, anyway:
I begin from Control Flow layer.
I€™ve created a sequence container and inside I€™ve got two groups, one own a sql task and another one own a Data Flow task. Both are linked for a completion conector. Up to here everything is fine. But when I collapse my sequence container the arrow remains there for these tasks and you can see the sequence container €œclosed€? and the arrow lonely.
Not very esthetic, not practical.
Any clarification or though will be as usual welcomed
View 6 Replies
View Related
Oct 7, 2015
 I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site.  Any recommendations on how to control the growth. - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?
View 7 Replies
View Related
Feb 13, 2008
Hi,
My question is if I close the sqlreader i am using. will that close the connection it uses or the connection will still remain open?
Syed
View 4 Replies
View Related
Nov 22, 2007
Hi all,
I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:
PROCEDURE NAME IS SPTOPTWENTYUSERS
SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS
WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY STRUSERNAME
ORDER BY INTDOWNLOAD DESC
The code that runs it is as follows:
sSQLString = SPTOPTWENTYUSERS
Using cnn As New SqlConnection(GetPath)
Try
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader
With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
cnn.Open()
dr = cmd.ExecuteReader
Any help on why this happens would be much appreciated.
thanks
View 1 Replies
View Related
Dec 3, 2006
I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)
A sample time series would look like this:
11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT
The result of the SQL query should be:
Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45
This is the SQL statement I came up with but obviously doesn't work:
SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)
I would appreciate any suggestions.
Thank you!
View 10 Replies
View Related
Aug 2, 2007
I made a website in ASP.net and using sql server 2005 as database. There is sometime processing data that need long time processing ( about 20 minutes ) and big data. It works fine in dev box, but when I place on shared hosting, and some people access it crashed. The website can not be accessed. Hosting support told me maybe I need to reprogram my code. So anybody has solution for this problem ? Should I create new thread ?
View 3 Replies
View Related
Feb 18, 2000
Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.
Thanks in Advance,
Bye,
Madhuker.
View 1 Replies
View Related
Jun 5, 2001
Hi there!,
Problem: I schedule a job that calls a stored procedure which loads around 1.5 million records. The Job takes 19 hrs to complete. However, if i run that stored procedure manually in Query Analyser it takes only 45 minutes..
Did anyone faced this problem? Is this known problem..Any suggestions/recommendations?
thank you.
regards,
Nana
View 6 Replies
View Related
Dec 5, 2014
I have a CTE query that is used to fill in nulls on a history table. The With statement executes just fine. sub 2 seconds on 974 records, however the main query is what's turning the whole query into a turtle. I know that it's the looping that it's doing there that is causing the slow down, but I'm just not sure how to fix it. I've tried inserting it into a temp table, refactored the code a hundred times, but nothing seems to be working.
Code is below and the execution plan is attached.
Server Version: 12.0.2342.0
Enterprise: 64bit
;WITH BuildTable
AS ( SELECT [GEGTH].[ID]
, [GEGTH].[Changed By]
, CAST( [dbo].[GetWeekStarting] ([GEGTH].[Changed Date] , 2 ) AS DATE) AS WeekOf
, [GEGT].[Title]
[code]....
View 6 Replies
View Related
Jun 2, 2008
I had a database of electronic resources which had 28000 records earlies and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.
Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable.
Thanks in advance,
Archana
View 2 Replies
View Related
Jul 23, 2005
When I login using QA to my SQL Server database, it takes 15-20 secondsto establish a connection and open a query window. Drilling into adatabase via Enterprise Manager is similar. Once the connection isestablished, the server runs plenty fast however.Can someone tell me why it could take a long time for a connection tobe established?This behavior occurs when I am local on the box.Thanks,John
View 1 Replies
View Related
Mar 21, 2008
Hi,I've a strange problem with a INSERT query. It's taking a long time toexecute. The format is like this :INSERT INTO table1SELECT ..FROM table2Executing the SELECT .. FROM table2 is taking 30 seconds. The resultis nothing: no records are selected.When i include the INSERT part it will take 12 hours to completeINSERT INTO table1SELECT ..FROM table2There's is an index on the table and when i delete it, it gives stillthe problem.Keh?Greetz,Hennie
View 1 Replies
View Related
Sep 4, 2007
Hi:
I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.
How can expediate my query execution process.
Environment details
Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.
Connect String in java code:
jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd
and use PreparedStatement and ResultSet.
Regards
Arup
View 2 Replies
View Related
May 16, 2007
I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.
use MyDatabase
GO
select db_name(database_id) as 'Database', o.name as 'Table',
s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',
avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,
schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s
join sys.objects o on s.object_id = o.object_id
join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
where db_name(database_id) = 'MyDatabase'
order by avg_fragmentation_in_percent desc
--order by avg_fragment_size_in_pages desc
--order by page_count desc
--order by record_count desc
--order by avg_record_size_in_bytes desc
View 4 Replies
View Related
