An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
-2
votes
1answer
743 views
What are performance benefits when using number type instead of using char/varchar?
From various posts, I have summarized about using number type instead of char/varchar for PK:
If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
1
vote
1answer
169 views
Using sys.dm_os_performance_counters and DIFF to display logins per second
PROBLEM
Running my script below i can see the number of logins as an accumulated number to my instance:
SELECT cntr_value AS [LoginsPerSec]
FROM sys.dm_os_performance_counters
WHERE
...
2
votes
1answer
518 views
Why Is oracle ignoring my index?
This query is not too complex:
select
trunc(created_date, 'MONTH') as created_date,
al.op_name,
al.region,
al.alarm_type,
COUNT(1) as total_new,
SUM(
(SELECT
...
10
votes
3answers
523 views
Where is the INNODB Performance Leak?
I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me.
The Situation
I am working on a system which handles a lot ...
3
votes
2answers
313 views
Help to understand explain plan in Oracle
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
2
votes
1answer
292 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
-1
votes
1answer
124 views
Multiple schema or multiple databases for better performance [closed]
I need to improve the performance of our enterprise .Net search application from the database point of view.
The app already has 4 schemas on SQL Server 2008.
2 schemas for front-end operations ...
1
vote
2answers
162 views
Multiple schema or multiple databases for better performance
I need to improve the performance of our entriprise search application(.net) from the database point of view.
The app already has 4 schemas on a separate sql server 2008.
2 schemas for front-end ...
6
votes
3answers
2k views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
7
votes
2answers
706 views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
12
votes
4answers
514 views
The order of columns in a table
From what I've seen so far, when we define a table, normally we put the primary key as the first column. If there are miscellaneous columns that are common in all tables, they come at the bottom of ...
8
votes
1answer
978 views
SQL Server TempDB behaviour in large memory environment
Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.
We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
3
votes
1answer
112 views
In-memory SQL Server log file
I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is ...
5
votes
2answers
367 views
SQL Server performing slow
I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM.
I have from 100 to 300 users working concurrently on the server.
Users complain about slowness of the application. ...
9
votes
4answers
781 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
8
votes
2answers
161 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
14
votes
4answers
1k views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
1
vote
3answers
381 views
Improve performance of a Fact Table
I have a Fact table CardTransactionFact
Table Structure
TABLE [dbo].[CardTransactionFact]
[CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionTerminalID] [int] NOT NULL,
...
0
votes
0answers
101 views
Reusing a query with multiple queries [closed]
Here is my query
select *
from
(select a.*, rownum rnum
from
(select id, data
from t
order by id) a
where rownum <= HIGHER
)
where rnum >= LOWER;
Say i want to use the innermost ...
1
vote
2answers
432 views
Querying a database efficiently for a huge chunk of data
Here is my query
SELECT *
FROM
(
SELECT a.*, rownum rnum
FROM
(
SELECT id, data
FROM t
ORDER BY id
) a
...
7
votes
2answers
162 views
High rise in SQL Server CPU after adding a DB
I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% ...
9
votes
2answers
627 views
Placing Transaction Log on Separate Volume [solid state]?
Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute ...
5
votes
2answers
181 views
Why you want to avoid Dynamic SQL in stored procedure?
I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
3
votes
2answers
298 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
3
votes
3answers
610 views
What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?
I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
4
votes
3answers
1k views
How database size affects performance: Theory vs reality
There is a lot out there that says database size should not affect performance to any great degree. As long as indexes on tables fit in memory the database should remain performant.
However what is ...
2
votes
0answers
2k views
Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded
Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
1
vote
1answer
120 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
3
votes
2answers
622 views
Rebuilding index has impact on mdf/ldf files SQL Server 2008
While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.
What does it exactly means?
Is complete process done on TempDB and does ...
4
votes
1answer
643 views
Analysing SQL Server Network I/O
A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link.
Ideally I'd look at some counters ...
2
votes
1answer
344 views
Fastest way to copy data from MyISAM to InnoDB
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try ...
3
votes
3answers
224 views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
0
votes
1answer
142 views
Performance comparison in ways a SSIS package can be run
I'm wondering if there is any difference in performance of a SSIS package if it runs using SQL Server job, vs running using DTEXECUI.EXE Utility or running in BIDS?
Also if the target database in on ...
2
votes
2answers
178 views
Writing to a DKNF database, but reading from a de-normalized table for performance?
Would the following approach be reasonable?
The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.
The Context: A database for ...
1
vote
3answers
78 views
Generally speaking, what pecking order should be used when tuning SQL Server?
When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to ...
1
vote
3answers
594 views
How can I speed up “show columns” on MySQL?
My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower.
...
2
votes
5answers
188 views
How to shard 14 gb of data for archival database?
We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records.
The ...
1
vote
2answers
288 views
Slow queries on SQL Server [closed]
We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the ...
1
vote
1answer
58 views
SQL - Need some advices for building a large db
currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:
Size: HDD has > 4tb to store filegroup if using partition tables.
...
2
votes
1answer
115 views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
2
votes
1answer
303 views
Tune postgres 9.1 to speedup deletes
I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.
I've seen in older posts the ...
4
votes
2answers
458 views
What are the differences between SQL I/O Sim and SQL IO tools?
I want to test my different SQL Server alternatives with a benchmark tool.
I found two tools by Microsoft:
SQLIO Disk Subsystem Benchmark Tool
SQL IO Sim
What are the differences between them? is ...
4
votes
4answers
187 views
Can I reorganise index on a sql server 2005 database without performance hit on production server
I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
3
votes
2answers
173 views
Are these MySQL queries efficient?
It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:
1) Are these doing what I expect
2) Are there ...
14
votes
5answers
3k views
What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?
I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
2
votes
1answer
91 views
why/how does the number of matched columns influences the way of excecuting a query
Imagine the following situation:
Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.
FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4
Table B uses InnoDB and ...
3
votes
2answers
2k views
Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?
I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?
I understand that where platform interoperability and/or backwards compatibility may be ...
4
votes
1answer
1k views
Why is DELETE so much slower than SELECT, then DELETE by id?
I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
0
votes
1answer
733 views
mysql closing tables taking double the time of process
I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table.
It is taking a long time to close tables.
ex: I run a query and the total time ...
3
votes
4answers
2k views
mysql insert into indexed table taking long time after few million records
I have a table like this
CREATE TABLE IF NOT EXISTS `dnddata` (
`numbers` varchar(10) NOT NULL,
`opstype` char(1) NOT NULL,
PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
...
